Howdy, Stranger!

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

Categories

Need help in copying and updating rows in excel

gxangelgxangel Member Posts: 1


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!

Comments

  • dokken2dokken2 Member Posts: 532
    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

    [code]
    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 WORKSHEETS
    Set WB = Application.Workbooks(1)
    Set WS1 = WB.Worksheets("ONE")
    Set WS2 = WB.Worksheets("TWO")

    'LOOP VALUES IN EXCEL ROWS/CELLS
    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
    'COMPARE WS-ONE TO WS-TWO
    If WS1.Rows.Cells(myRow, 1) <> WS2.Rows.Cells(myRow, 1) Then
    'LOOP THRU 15 COLUMNS
    For myCol = 1 To 15
    'COPY TWO INTO ONE
    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
    [/code]
Sign In or Register to comment.