VBA to copy multiple ranges based on different criteria

[goose83]

Hi All,
I appreciate the time you will take in helping me address this issue. I have been at this for hours but cant figure it out. I have a sheet (Cert Responses) that is auto populated from survey responses. The cell ranges are A:BO

All I am trying to do is copy values starting in A:K and L:Q if column K has a "Yes" Value and place those cells in a new sheet (Cert Cleaned)
Also, Starting checking Column R for "yes" value and if condition is met then copy A:K & S:Y to a new row on the new sheet
If K or R are not equal "Yes" move to next row

Below is an example layout and expected outcome as well as the code I have and cant get it to accommodate multiple ranges
***Cert Responses*****
|A |B |C |D |E |F |G |H |I |J |K |L |M |N |O |P |Q |R |S |T |U |V |W |X |Y
|George|Smith|value|value|value|value|value|value|value|value|Yes |value|value|value|value|value|value |Yes (R) |value 1|value 1|value 1|value 1|value 1|value 1|value 1|

The result Should look like this
***Cert Cleaned***
|A |B |C |D |E |F |G |H |I |J |K |L |M |N |O |P |Q
|George|Smith|value|value|value|value|value|value|value|value|Yes |value |value |value |value |value |value |
|George|Smith|value|value|value|value|value|value|value|value|Yes (R) |value 1|value 1|value 1|value 1|value 1|value 1|

***Code****
Sub Cpy()
Dim LR As Long, i As Long, Done As Boolean
With Sheets("Cert Responses")
LR = .Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LR
If .Range("K" & i).Value = "Yes" Then
.Range("B2" & i).Resize(, 16).Copy
If Done Then
Sheets("Cleaned_Responses").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
Else
Sheets("Cleaned_Responses").Range("A2").PasteSpecial Paste:=xlPasteValues
Done = True
End If
End If
Next i
End With
End Sub

 

[footoo]

If it is OK to sort the Cert Cleaned sheet (say by column B), suggest you base the code on the following (no loops required) :
• Filter column K for "Yes", select visible cells in A:Q, copy and paste to sheet Cert Responses
• Filter column R for "Yes", select visible A:K cells & copy/paste, select visible S:Y cells & copy/paste
• Sort the Cert Cleaned sheet by column B

 

[goose83]

Hi Footoo, I appreciate the response Do you happen to have any sample code you can share with me.

If it is OK to sort the Cert Cleaned sheet (say by column B), suggest you base the code on the following (no loops required) :
• Filter column K for "Yes", select visible cells in A:Q, copy and paste to sheet Cert Responses
• Filter column R for "Yes", select visible A:K cells & copy/paste, select visible S:Y cells & copy/paste
• Sort the Cert Cleaned sheet by column B

 

[footoo]

The code can be obtained from the macro recorder and tweaked to replace hard-coded range addresses.