Posts by Shaner73

    Re: Copy and Past Row based on criteria??


    The data is actually there...most of the cells are drop-downs. The only information a person will be actually typing is the Date and the Consumed Qty. Everything else is picking from the drop-down or the Macro will be auto-populating the other cells.


    The trigger will be when whatever cell in Column "O" becomes "Yes", then it will just copy and paste the cells from Columns F and G of the current Row into the "Order" sheet....(make sense?)


    I have attached a new version on the workbook as I've changed a couple things in the formulas. If you enter 3500 into "H2", this will cause O2 to be "Yes".

    Re: Copy and Past Row based on criteria??


    Quote from skywriter;771399

    But in your original post you mentioned down to 500.


    So F2 gets copied if O2 is yes and F3 gets copied if O3 is yes, or
    F2 and F3 if O2 is yes, I'm confused?


    I did post that I had a typo...my brain was toast yesterday. Again, my apologies.


    The cells to be copied are F2 and G2 IF O2 is "Yes". And this will continue as Rows are populated down to Row 500.

    Re: Copy and Past Row based on criteria??


    Quote from skywriter;771329

    Why 2 cells F2 and F3, what's the logic? - Because this is the only data I need copied.


    What do you mean based on column O, do you mean based on O2 and O3 since your request is for F2 and F3 being copied. - It would be based on cell O2 for the data to be copied.


    Why does it matter if O has conditional formatting, if we are looking for a yes? - I just wanted to be thorough in the info I gave. Not only is there a Conditional Format, there is also a Formula.


    Answered your questions.


    I don't have a lot of experience with VBA...mostly very basic things.

    I've tried several ways to do this, and have Google searched for a couple days, but still coming up empty.


    I'm needing to copy cells "F2" and "F3" from Sheet "InvTraker", then pasted into cells "A2" and "B2" into sheet "Order", based on Column "O" being "Yes". However, Column "O" is a formula with a Conditional Format via a macro. This will need to continue all the way down the sheet (row 500)...which may change.


    I have attached the workbook for reference.


    forum.ozgrid.com/index.php?attachment/69224/

    Re: Converting macro built in '07 to use in '03...


    Thank you cytop. I made some adjustments and the work great. Now, I will have to get on another PC to work with 2003 and step through the code.


    If anyone else has some advice or direction, I'm all ears...well, eyes.

    I know there are compatibility issues between Excel 2003 and 2007. So, I'm pretty sure I already know my answer, but I need to ask so I can rest easy.


    A co-worker built a macro in 2007. However, a majority of my company does not have 2007, only 2003. These people need this macro loaded on their PC, but the macro doesn't work on 2003.


    Is there an easy "fix", or does the code need to be re-written in 2003?


    Here's the code: (suggestions on making this run faster and more efficient are welcome)


    Re: Speed up and Refine Macro


    Well, everything seems to be a big mess now. I'm not sure what happened. Back to the drawing board I guess.


    The info in Col. A is now gone
    The info to be pulled from Col. A and put into columns N, S and T is not there.


    I was going to attach the current codes, but 5 of them are too large.


    Thanks for the help anyway.

    Re: Speed up and Refine Macro


    Ok, let me work on this and see what I can get to work, or not work. I really appreciate all of your help.


    And you're correct, I am no comfortable with VBA. But I will be one of these days.

    Re: Slow Macro / Macro goes to last row in sheet, not to the end of data


    Thank you for the quick response. But, I am now getting an error...


    Re: Slow Macro / Macro goes to last row in sheet, not to the end of data


    Fifth process....the Second-Forth process are virtually the same....just different "rules".


    [/COLOR][/SIZE][/FONT]

    Re: Slow Macro / Macro goes to last row in sheet, not to the end of data


    Second process continued...


    [/COLOR][/SIZE][/FONT]

    Re: Slow Macro / Macro goes to last row in sheet, not to the end of data


    Second process...this will take two posts...


    Code
    Sub B()'' B Macro' Macro recorded 6/28/2010''Moves_Col_A_Temporarily_to_Col_CZ_to_work_on    Columns("A:A").Select    Selection.Copy    Range("CZ1").Select    ActiveSheet.Paste        Columns("CZ:CZ").Select           '*** FIND AND REMOVE SUFFIXES ***'*********************************************'*** MACRO STARTS AGAIN FURTHER BELOW ***'************************************************************'ITEMS TO REMOVE FROM ORIGINAL'***  REV SUFFIX BEGINS ***    Selection.Replace What:=" (3649898)**", Replacement:="~(3649898)*", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:=" (AKA 108613258)*", Replacement:="~(AKA 108613258)", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:=" (AMK DIE0.11)*", Replacement:="~(AMK DIE0.11)", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:=" (B) TYPE*", Replacement:="~(B) TYPE", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:=" (B88069X1630T602)*", Replacement:="~(B88069X1630T602)", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="W/TRIMMED LEAD*", Replacement:="~TRIMMED LEADS", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="White*", Replacement:="~White", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="Yellow*", Replacement:="~Yellow", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False        '***  REV SUFFIX ENDS ***'*** MACRO CONTINUES  ***'**********************************'Separates the suffixes from the MPN    Application.CutCopyMode = False    Selection.TextToColumns Destination:=Range("CZ1"), DataType:=xlDelimited, _        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _        :="~", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True ' Insert_Col_DA' Macro recorded 5/29/2009 by abunch'    Columns("DA:DA").Select    Selection.Insert Shift:=xlToRight '' Format_Col_DA_to_General' Macro recorded 5/29/2009 by abunch'    Columns("DA:DA").Select    Selection.NumberFormat = "General"  '' Concatenate_Formula' Macro recorded 5/29/2009 by abunch'    Range("DA2").Select'Formula: =CONCATENATE(DD2,DE2,DF2)    ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[1],RC[2],RC[3])"    Range("DA2").Select  'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxIf IsEmpty(ActiveCell) Then Exit Sub  Range(ActiveCell, ActiveCell.Offset(0, -1).End(xlDown).Offset(0, 1)).FillDown    Range("da2").Select      'Insert_Column_CZ_to_KEEP_"REV"_Prefixes    Columns("CZ:CZ").Select    Selection.Insert Shift:=xlToRight    With Selection        .HorizontalAlignment = xlLeft        .VerticalAlignment = xlBottom        .WrapText = False        .Orientation = 0        .AddIndent = False        .IndentLevel = 0        .ShrinkToFit = False        .ReadingOrder = xlContext        .MergeCells = False    End With    'Insert_Column_DB_to_KEEP_"REV"_Prefixes    Columns("DB:DB").Select    Selection.Insert Shift:=xlToRight    With Selection        .HorizontalAlignment = xlLeft        .VerticalAlignment = xlBottom        .WrapText = False        .Orientation = 0        .AddIndent = False        .IndentLevel = 0        .ShrinkToFit = False        .ReadingOrder = xlContext        .MergeCells = False    End With    'Formula in CZ2: =IF(DG2<>"",DG2,IF(DA2="","",DA2))    Range("CZ2").Select    ActiveCell.FormulaR1C1 = "=IF(RC[7]<>"""",RC[7],IF(RC[1]="""","""",RC[1]))"      'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx  Range("CZ2").Select  If IsEmpty(ActiveCell) Then Exit Sub  Range(ActiveCell, ActiveCell.Offset(0, -1).End(xlDown).Offset(0, 1)).FillDown    Range("cz2").Select    'Formula in DB2: =IF(DG2<>"","",DC2)    Columns("DB:DB").Select    Application.CutCopyMode = False    'Change Col DB to General formatting    Selection.NumberFormat = "General"        Range("DB2").Select    ActiveCell.FormulaR1C1 = "=IF(RC[5]<>"""","""",RC[1])"    Range("DB2").Select    'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx  If IsEmpty(ActiveCell) Then Exit Sub  Range(ActiveCell, ActiveCell.Offset(0, -1).End(xlDown).Offset(0, 1)).FillDown    Range("db2").Select        'Formula in DG2: =IF(MID(A2,1,3)="REV",A2,"")'(Formula checks to see if the first 3 letters are "REV")    Range("DG2").Select    ActiveCell.FormulaR1C1 = "=IF(MID(RC[-110],1,3)=""REV"",RC[-110],"""")"    Range("DG2").Select    Selection.Copy    Range("DG3").Select    Range("DG3").Select    ActiveSheet.Paste    Application.CutCopyMode = False    Range("DG1").Select    Range("CZ1").Select    ActiveCell.FormulaR1C1 = "=RC[1]"    Range("CZ2").Select    Columns("CZ:CZ").Select    Selection.Copy    Range("A1").Select    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _        :=False, Transpose:=False    Columns("DB:DB").Select    Application.CutCopyMode = False    Selection.Copy    Application.CutCopyMode = False    Range("DB1").Select    ActiveCell.FormulaR1C1 = "=RC[-95]"    Columns("DB:DB").Select    Selection.Copy    Range("N1").Select    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _         :=False, Transpose:=False