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.
Re: Copy and Past Row based on criteria??
Why 2 cells F2 and F3, what's the logic?
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.
Why does it matter if O has conditional formatting, if we are looking for a yes?
Re: Copy and Past Row based on criteria??
Quote from skywriter;771329Why 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.
Re: Copy and Past Row based on criteria??
Sorry...I just noticed my typo...it should be cells F2 and G2. My apologies
Re: Copy and Past Row based on criteria??
QuoteWhy 2 cells F2 and F3, what's the logic? - Because this is the only data I need copied.
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?
Re: Copy and Past Row based on criteria??
Quote from skywriter;771399But 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??
Okay a couple of things.
Since you are using a table it's easy to write code that expands as you add more data to the table.
Your sample contains no data, so if don't have 500 rows of data I would recommend you add rows as needed and your formulas will autofill, therefore no need to have a lot of blank rows.
I see you have worksheet change event code. Normally this is the kind of event that we would want to trigger the code we need to run.
Unfortunately the code doesn't get triggered when the change is the result of a formula calculating.
A solution would be to run the code only when you want to.
Otherwise, we need to figure out how you want to trigger the code and if you want it to be triggered often then the fact that it has to look at 500 rows may slow your workbook down.
The other issue is the data in the order sheet.
If you want to clear the data sheet via code each time you run it and replace that data it's not a big deal. If you want to append data that's not already on that sheet, then we need additional code to check if the product already exists on the sheet before we send it to the order sheet.
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??
QuoteThe 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?)
As I explained a formula calculating will not trigger the worksheet change event.
QuoteI 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".
Then the code will be triggered by someone changing a value in the H column.
That is what I will write.
Re: Copy and Past Row based on criteria??
Try this. :cool:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.ListObjects("InvTracker").ListColumns("Consumed Qty").DataBodyRange) Is Nothing Then
If Me.ListObjects("InvTracker").DataBodyRange(Target.Row - 1, 15).Value = "Yes" Then
Dim lr As Long
lr = Worksheets("Order").Cells(Rows.Count, "A").End(xlUp).Offset(1).Row
Me.Range(Target.Offset(, -2), Target.Offset(, -1)).Copy
Worksheets("Order").Range("A" & lr).PasteSpecial (-4163)
Application.CutCopyMode = False
End If
End If
Application.EnableEvents = False
'Formulas
Range("C2:C500").Formula = "=IF(B2="""","""",B2+7-WEEKDAY(B2,1)+1)"
Range("G2:G500").Formula = "=IF(F2="""","""",VLOOKUP([@[Product Name]],'Vend_Prod (2)'!C:D,2,FALSE))"
Range("I2:I500").Formula = "=IF(G2="""","""",VLOOKUP(G2,'Vend_Prod (2)'!D:E,2,FALSE))"
Range("J2:J500").Formula = "=IF(H2="""","""",VLOOKUP([@[Product Name]],MaxStockQty!A:E,5,FALSE))"
Range("K2:K500").Formula = "=IF(F2="""","""",VLOOKUP([@[Product Name]],'Vend_Prod (2)'!C:H,6,FALSE))"
Range("L2:L500").Formula = "=IF(F2="""","""",VLOOKUP([@[Product Name]],'Vend_Prod (2)'!C:G,5,FALSE))"
Range("M2:M500").Formula = "=IF(H2="""","""",VLOOKUP([@[Product Name]],'Pivot for Avg Use'!A:D,3,FALSE))"
Range("N2:N500").Formula = "=IF(M2="""","""",J2/M2)"
Range("O2:O500").Formula = "=IF(N2="""","""",IF(J2<=13000,""No"",""Yes""))"
Application.EnableEvents = True
'Pivot Table auto refresh
Worksheets("Pivot for Avg Use").PivotTables("PivotTable3").PivotCache.Refresh
End SubRe: Copy and Past Row based on criteria??
Thanks for telling me which line it's on, but I always like to point out to people that errors have numbers and descriptions.
So when sharing that you have an error, please give the number, description and the line that it occurred on.
Re: Copy and Past Row based on criteria??
This info?
Run-time error '13':
Type mismatch
Re: Copy and Past Row based on criteria??
So you change for instance H2, the code runs and what is the value in O2 when you get the error?
Re: Copy and Past Row based on criteria??
As soon as I enter data into H2 and tab over, the Error pops up. In O2, the formula does NOT appear to complete. The cell contains #DIV/0!. I have attached a pic.
Re: Copy and Past Row based on criteria??
Never mind, maybe we flip flop the code that was existing and the code that I wrote.
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
'Formulas
Range("C2:C500").Formula = "=IF(B2="""","""",B2+7-WEEKDAY(B2,1)+1)"
Range("G2:G500").Formula = "=IF(F2="""","""",VLOOKUP([@[Product Name]],'Vend_Prod (2)'!C:D,2,FALSE))"
Range("I2:I500").Formula = "=IF(G2="""","""",VLOOKUP(G2,'Vend_Prod (2)'!D:E,2,FALSE))"
Range("J2:J500").Formula = "=IF(H2="""","""",VLOOKUP([@[Product Name]],MaxStockQty!A:E,5,FALSE))"
Range("K2:K500").Formula = "=IF(F2="""","""",VLOOKUP([@[Product Name]],'Vend_Prod (2)'!C:H,6,FALSE))"
Range("L2:L500").Formula = "=IF(F2="""","""",VLOOKUP([@[Product Name]],'Vend_Prod (2)'!C:G,5,FALSE))"
Range("M2:M500").Formula = "=IF(H2="""","""",VLOOKUP([@[Product Name]],'Pivot for Avg Use'!A:D,3,FALSE))"
Range("N2:N500").Formula = "=IF(M2="""","""",J2/M2)"
Range("O2:O500").Formula = "=IF(N2="""","""",IF(J2<=13000,""No"",""Yes""))"
Application.EnableEvents = True
'Pivot Table auto refresh
Worksheets("Pivot for Avg Use").PivotTables("PivotTable3").PivotCache.Refresh
If Not Intersect(Target, Me.ListObjects("InvTracker").ListColumns("Consumed Qty").DataBodyRange) Is Nothing Then
If Me.ListObjects("InvTracker").DataBodyRange(Target.Row - 1, 15).Value = "Yes" Then
Dim lr As Long
lr = Worksheets("Order").Cells(Rows.Count, "A").End(xlUp).Offset(1).Row
Me.Range(Target.Offset(, -2), Target.Offset(, -1)).Copy
Worksheets("Order").Range("A" & lr).PasteSpecial (-4163)
Application.CutCopyMode = False
End If
End If
End SubRe: Copy and Past Row based on criteria??
That worked!!! Thank you so much. I really appreciate your help!
Re: Copy and Past Row based on criteria??
My pleasure, thanks for the feedback. :cool:
Don’t have an account yet? Register yourself now and be a part of our community!