Excel Compare Worksheet Macro

Create new macro, press Tools -> macro -> Visual Basic Editor
Click on the worksheet on the left to select which worksheet you want to insert the macro.
Start writing your macro on the right editor. Below is the macro to compare 2 weeksheets and copy the value if the values matches.

Sub CopyBasedonSheet1()

Dim i As Long
Dim j As Long
Sheet1LastRow = Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
Sheet2LastRow = Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
Worksheets("Sheet1").Cells(1, 3).Value = Sheet1LastRow
Worksheets("Sheet1").Cells(1, 4).Value = Sheet2LastRow

For j = 1 To Sheet1LastRow
    For i = 1 To Sheet2LastRow
        If Worksheets("Sheet1").Cells(j, 1).Value = Worksheets("Sheet2").Cells(i, 1).Value Then
            Worksheets("Sheet1").Cells(j, 2).Value = Worksheets("Sheet2").Cells(i, 2).Value
            Exit For
        End If
    Next i
    Application.StatusBar = j
    Worksheets("Sheet1").Cells(1, 5).Value = j
Next j
End Sub

To run macro, press Tools -> macro -> macros -> Select the macro you wish to run

To terminate macro, press "command" and "." together.

Be the first to comment

Leave a Reply

Your email address will not be published.