Macro to copy and paste rows based on cell value

[uncleslinky]

Hi


I'm looking to automate a small copy & paste process with a macro but I'm having problems knowing where to start. I can record a macro to figure out some of copy and paste parts but I'd also need an if statement.


I have Sheet 1 where I want to look in column B to see if the value "YES" occurs, if so I want to copy the 8 adjacent cells - not including column B - so from column C:J for that row. This would then need to be pasted in Sheet 2, Column A at the next available Row.


I would then need to restart the process but this time instead of looking in Column C on Sheet 1 and, I'd need to now look in Column M for "YES" then copy the next 8 adjacent cells as before and again paste values to the next available row in Sheet 2.


I hope this makes sense (and that I've not missed any detail)
Any input would be great


Thanks
Rich

 

[JLGWhiz]

Sub copyStuff()
Dim sh1 As Worksheet, sh2 As Worksheet, lr As Long
Set sh1 = Sheets(1) 'Edit sheet name
Set sh2 = Sheets(2) 'Edit sheet name
lr = sh1.Cells.Find("*", , xlFormulas, xlPart, xlByRows, xlPrevious).Row
    With sh1
        .Range("B1:J" & lr).AutoFilter 1, "Yes"
        .Range("C2:J" & lr).SpecialCells(xlCellTypeVisible).Copy sh2.Cells(Rows.Count, 1).End(xlUp)(2)
        .AutoFilterMode = False
        .Range("M1:U" & lr).AutoFilter 1, "Yes"
        .Range("N2:U" & lr).SpecialCells(xlCellTypeVisible).Copy sh2.Cells(Rows.Count, 1).End(xlUp)(2)
        .AutoFilterMode = False
    End With
End Sub

Goes in standard code module 1.

 

[uncleslinky]

Hi

Thanks for the reply and solution, that's working great
Can I just ask one more thing, I forgot to cover error checking - is there a way to if "YES" cannot be found in Column B then move on to the next part of the code, as at the moment an error is occurring when "YES" is not present?

Thanks again

 

[JLGWhiz]

Hi

Thanks for the reply and solution, that's working great
Can I just ask one more thing, I forgot to cover error checking - is there a way to if "YES" cannot be found in Column B then move on to the next part of the code, as at the moment an error is occurring when "YES" is not present?

Thanks again

Sub copyStuff()
Dim sh1 As Worksheet, sh2 As Worksheet, lr As Long
Set sh1 = Sheets(1) 'Edit sheet name
Set sh2 = Sheets(2) 'Edit sheet name
lr = sh1.Cells.Find("*", , xlFormulas, xlPart, xlByRows, xlPrevious).Row
    With sh1
        If Application.CountIf(.Range("B:B"), "yes") > 0 Then
            .Range("B1:J" & lr).AutoFilter 1, "Yes"
            .Range("C2:J" & lr).SpecialCells(xlCellTypeVisible).Copy sh2.Cells(Rows.Count, 1).End(xlUp)(2)
        End If
        .AutoFilterMode = False
        If Application.CountIf(.Range("M:M"), "yes") > 0 Then
            .Range("M1:U" & lr).AutoFilter 1, "Yes"
            .Range("N2:U" & lr).SpecialCells(xlCellTypeVisible).Copy sh2.Cells(Rows.Count, 1).End(xlUp)(2)
        End If
        .AutoFilterMode = False
    End With
End Sub

 

[uncleslinky]

Thanks :D

 

[JLGWhiz]

Thanks :D

You're welcome,
Regards, JLG