Copy certain columns from one spreadsheet to another, based on criteria in one column

[Mldeuser]

Hello

I have a workbook with multiple spreadsheets. I would like to copy columns from Sheet A to Sheet B based on criteria found in column G on Sheet A.

Sheet A if any row in Column G has UAC then copy the information for that row found in columns B through H to, columns B through H on Sheet B.

Thank you
Mark

 

[alansidman]

Option Explicit


Sub testX()
    Dim s1 As Worksheet, s2 As Worksheet
    Set s1 = Sheets("A")
    Set s2 = Sheets("B")
    Dim i As Long, lr As Long, lrB As Long
    Application.ScreenUpdating = False
    lr = s1.Range("G" & Rows.Count).End(xlUp).Row
    For i = 1 To lr
        If s1.Range("G" & i) = "UAC" Then
            s1.Range("B" & i & ":H" & i).Copy
            lrB = s2.Range("B" & Rows.Count).End(xlUp).Row + 1
            s2.Range("B" & lrB).PasteSpecial xlPasteValues
        End If
    Next i
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    MsgBox "completed"




End Sub

 

[Mldeuser]

It worked perfectly

Thank you