In excel how to compare A columns in 2 Sheets and copy matching rows to Sheet3?

Question

I'm new to Visual Basic and I want help to try something.

I want to compare all A columns from Sheet1 to A columns from Sheet2 and if exact match is found than copy the entire matching rows from Sheet2 to Sheet3.

Thanks in advance.

Answer 1

Modified version of my answer from This Question

Sub CopyMatch()

Dim StartingScreenUpdateValue As Boolean
Dim StartingEventsValue As Boolean
Dim StartingCalculations As XlCalculation

With Application
    StartingScreenUpdateValue = .ScreenUpdating
    StartingEventsValue = .EnableEvents
    StartingCalculations = .Calculation
    .ScreenUpdating = False
    .EnableEvents = False
    .Calculation = xlCalculationManual
End With


Dim varTestValues As Variant
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim sh3 As WorkSheet

Set sh1 = Sheets("Sheet1")
Set sh2 = Sheets("Sheet2")
Set sh3 = Sheets("Sheet3")

With sh2
    varTestValues = .Range("A1", .Range("A" & .Rows.Count).End(xlUp))
End With

With sh1
    .Range("A1", .Range("A" & .Rows.Count).End(xlUp)) _
    .AutoFilter Field:=1, Criteria1:=Application.Transpose(varTestValues), Operator:=xlFilterValues

    .Range("A2", sh1.Range("A" & .Rows.Count).End(xlUp)) _
    .SpecialCells(xlCellTypeVisible).EntireRow.Copy sh3.Range("A1")

    .AutoFilterMode = False
End With

With Application
    .ScreenUpdating = StartingScreenUpdateValue
    .EnableEvents = StartingEventsValue
    .Calculation = StartingCalculations
End With

End Sub

NOTE: This code runs assuming your data has headers if it does not please advise.

REMEMBER Always run any code on a copy of your data and not your actual data until you are confident that it is working 100%.