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.
Leave a Reply