VBA to copy an entire row based on 2 criteria

[JKast]

Hi all,

I'm currently using this code to copy data from a Main sheet, based on the "x", "y" and "z" criteria in column Y. I need this code modified to also look at criteria in column N. The options in column N are "OH", "OHD", "RGS", "PTD" and "TBD". In order to be copied Column N must be either "OH", "OHD" or "RGS"

I have 3 different worksheets X, Y and Z that I want each row copied into if it meets both criteria. For example, copy entire row from the Main sheet into worksheet X if column Y is x and column N is any of the three "OH", "OHD or "RGS".

Sub CopyPasteData()
Dim a As Long, x As Long, y As Long
x = Sheets("Main").Cells(Rows.Count, 25).End(xlUp).Row
For a = 2 To x
Sheets("Main").Rows(a).Copy
b = Sheets("Main").Cells(a, 25)
y = Sheets(b).Cells(Rows.Count, 1).End(xlUp).Row
Worksheets(b).Range("A" & y + 1).PasteSpecial
Next a
End Sub

 

[Trevor G]

Can you work with this?

Sub cpy()
'Copy row data to another sheet, change sheet names Also you can add more variables
Dim LR As Long, i As Long
With Sheets("Sheet1")
LR = .Range("K" & Rows.Count).End(xlUp).Row
For i = 1 To LR
If .Range("K" & i).Value = "Yes" Then .Rows(i).Copy Destination:=Sheets("Action").Range("A" & Rows.Count).End(xlUp).Offset(1)
Next i
End With
End Sub

 

[JKast]

Thanks, I'm new to VBA but I'll see what I can do with it.

 

[Trevor G]

Look at this line in the code and you would use an Or statement something like this

.Range("K" & i).Value = "Yes" [COLOR=red]Or .Range("K" & i).Value = "No"[/COLOR]

 

[JKast]

Okay thanks. Basically the logic I'm trying to give it is to copy the entire row into worksheet X if column Y is X and column N is OH OHD or RGS.

Copy into worksheet Y if column Y is Y and column N is OH OHD or RGS

Copy into worksheet Z if column Y is Z and column N is OH OHD or RGS

I'll play around with what you gave me, thanks for the help.