CODEWITHSUNDEEP
excelvba
GeoffDavis021
GeoffDavis021

Reputation: 51

Compare Two Worksheets and Copy Row Data

I would like to compare two worksheets in one workbook. Some pseudocode:

If Cell A on Worksheet1 = Cell A on Worksheet2 And
If Cell F on Worksheet1 <> Cell F on Worksheet2 Then
Copy Row from Worksheet2 over the Row on Worksheet1 Else
If Cell A on Worksheet1 <> Cell A on Worksheet2 Then
Copy Row from Worksheet2 to next blank Row on Worksheet1

This is what I have so far:

Sub CopyCells()
Dim sh1 As Worksheet, sh2 As Worksheet
Dim j As Long, i As Long, lastrow1 As Long, lastrow2 As Long


Set sh1 = Worksheets("Sheet1")
Set sh2 = Worksheets("Sheet2")

lastrow1 = sh1.Cells(Rows.Count, "A").End(xlUp).Row
lastrow2 = sh2.Cells(Rows.Count, "A").End(xlUp).Row

For i = 2 To lastrow1
    For j = 1 To lastrow2
        If sh1.Cells(i, "A").Value = sh2.Cells(j, "A").Value And sh1.Cells(i, "F").Value <> sh2.Cells(j, "F").Value Then
            sh1.Cells(i, "F").Value = sh2.Cells(j, "F").Value
        End If
    Next j
Next i

End Sub

Upvotes: 0

Views: 92

Answers (1)

Alex P
Alex P

Reputation: 12497

Try this:

Sub CopyCells()
    Dim sh1 As Worksheet, sh2 As Worksheet
    Dim i As Long, j As Long, lastrow1 As Long, lastrow2 As Long, counter As Long

    Set sh1 = Worksheets("Sheet1")
    Set sh2 = Worksheets("Sheet2")

    lastrow1 = sh1.Cells(Rows.Count, "A").End(xlUp).Row
    lastrow2 = sh2.Cells(Rows.Count, "A").End(xlUp).Row
    counter = 1

    For i = 1 To lastrow1
        For j = 1 To lastrow2
            If sh1.Range("A" & i) = sh2.Range("A" & j) And sh1.Range("F" & i) <> sh2.Range("F" & j) Then
                sh2.Range("A" & j).EntireRow.Copy Destination:=sh1.Range("A" & i)
            ElseIf sh1.Range("A" & i) <> sh2.Range("A" & j) Then
                sh2.Range("A" & j).EntireRow.Copy Destination:=sh1.Range("A" & lastrow1 + counter)
                counter = counter + 1
            End If
        Next j
    Next i
End Sub

Upvotes: 0

Related Questions