Need help in copying and updating rows in excel

Hi, I'm a newbie in using VBA Excel and have never use VBA excel before and am totally lost in my problem. Thus, i would GREATLY appreciate a direction or two.

I have 2 worksheets ONE and TWO. Both have the same columns type and column number (approximately 15) with worksheet TWO being the more updated worksheet. I need to find the rows in worksheet TWO which are not present in worksheet ONE by comparing column A (contains an ID number) in both worksheets. After which i will copy the whole row from worksheet TWO to worksheet ONE. During the process of comparing column A in both worksheets, i also need to update column C and E of worksheet ONE by comparing these 2 columns using column A.

I can do the above without using VBA but i need to create a macro for it to automate the whole process for future uses.

Would appreciate any help greatly! Thanks in advance!


  • this compares sheet ONE to TWO, and copies TWO to ONE if the values in the first column of TWO are not Null or empty. easy to modify this to compare other columns

    Private Sub CopyWS()
    Const ColX = 1 'COL=1=A, COLUMN TO COMPARE

    Dim myCol As Integer, myRow As Integer
    Dim WB As Workbook
    Dim WS1 As Worksheet, WS2 As Worksheet

    Set WB = Application.Workbooks(1)
    Set WS1 = WB.Worksheets("ONE")
    Set WS2 = WB.Worksheets("TWO")

    For myRow = 1 To 15 'ROW 1-THRU-15
    'IS WS-TWO Null or Empty?, IF SO DONT COPY VALUE
    If WS2.Rows.Cells(myRow, ColX) <> Null Or WS2.Rows.Cells(myRow, ColX) <> "" Then
    If WS1.Rows.Cells(myRow, 1) <> WS2.Rows.Cells(myRow, 1) Then
    For myCol = 1 To 15
    WS1.Rows.Cells(myRow, myCol) = WS2.Rows.Cells(myRow, myCol)
    Next myCol
    End If
    End If
    Next myRow

    Set WS2 = Nothing
    Set WS1 = Nothing
    Set WB = Nothing
    End Sub
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!


In this Discussion