Home » Microsoft Office » Excel Macro which copies one column to another by matching specific unique column

Excel Macro which copies one column to another by matching specific unique column

This is a simple Excel Macro which copy one column to another by comparing specific unique column. There two sheets: Sheet1 and Sheet2. We copy column 2 and 3 at Sheet2 to column 2 and 3 at Sheet1 by matching column 1 at both.

There might be persentage data. You should use NumberFormat parameter in that situation.

Sub CopyCells()
    '*** Defining variables ***'
    Dim Value1, Value2
    Dim RowsCount1, RowsCount2

    '*** Find out rows count ***'
    RowsCount1 = Worksheets("Sheet1").Range("A1").CurrentRegion.Rows.Count
    RowsCount2 = Worksheets("Sheet2").Range("A1").CurrentRegion.Rows.Count

    '*** Copying data ***'
    For C1 = 2 To RowsCount2
Set Value1 = Worksheets("Sheet2").Cells(C1, 1)
For C2 = 3 To RowsCount1
Set Value2 = Worksheets("Sheet1").Cells(C2, 1)
If Value1 = Value2 Then
                Worksheets("Sheet1").Cells(C2, 2) = Worksheets("Sheet2").Cells(C1, 2)
                Worksheets("Sheet1").Cells(C2, 2).NumberFormat = "0.00%"
                Worksheets("Sheet1").Cells(C2, 3) = Worksheets("Sheet2").Cells(C1, 3)
                Worksheets("Sheet1").Cells(C2, 3).NumberFormat = ""
            End If
        Next C2
    Next C1
End Sub

C1 and C2 mean Counter1 and Counter2.

Ned Sahin

Blogger for 20 years. Former Microsoft Engineer. Author of six books. I love creating helpful content and sharing with the world. Reach me out for any questions or feedback.

Leave a Comment