How to Paste copied Row from one Sheet to Another

1
votes

I have two Excel worksheets: Sheet1 and Sheet2. Sheet2 is the master list while Sheet1 is the updated worksheet I receive from the system. What I need is to compare each value of Col A of Sheet1 with Sheet2. If there is a match, then I want to copy the entire matching row from Sheet1 and paste values from that row to the corresponding ColA value (Item#) row of Sheet2. Example shown below:

Sheet1 Worksheet

ColA                                      ColB

Item#                                     Updated Cost

1234                                      $30

Sheet2 Worksheet

ColA                                      ColB

Item#                                     Current Cost

1234                                      $45

There are more columns in my file than shown here, so its necessary to copy the entire row with the corresponding row in Sheet2. I started the needed Excel VBA code, but I'm stuck on the part to paste corresponding values in Sheet2. My code is pretty basic and it isn't working yet, so any help related to coding is appreciated.

Sub Macro1()
'
' Macro1 Macro
'
'   Copies corresponding item# rows from sheet1 worksheet
'   to sheet2 worksheet by comparing item# column

Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ColA As String
Dim rng1 As Range
Dim rng2 As Range
Dim RowCounter1 As Integer
Dim RowCounter2 As Integer

ColA = "A"

RowCounter1 = 2
RowCounter2 = 2

Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")

Do While Not IsEmpty(ws1.Range(ColA & RowCounter1).Value)

    Set rng1 = ws1.Range(ColA & RowCounter1)

    RowCounter2 = 1
    Do While Not IsEmpty(ws2.Range(ColA & RowCounter2).Value)

        Set rng2 = ws2.Range(ColA & RowCounter2) 
        If rng1.Value = rng2.Value Then 
             Rows(RowCounter1).EntireRow.Copy                  
             RowCounter2 = RowCounter2 - 1  
        End If
        RowCounter2 = RowCounter2 + 1

    Loop
    RowCounter1 = RowCounter1 + 1
Loop

End Sub
3
excelvba

3 Answers

1
votes

Here's a way on how to use the PasteSpecial method and some code simplification:

Sub Macro1()

'
' Macro1 Macro
'
'   Copies corresponding item# rows from sheet1 worksheet
'   to sheet2 worksheet by comparing item# column

Dim rng1 As Range, rng2 As Range

For Each rng1 In Worksheets("Sheet1").Range("A2").Resize(Worksheets("Sheet1").Range("A2").CurrentRegion.Rows.Count - 1).Rows
  For Each rng2 In Worksheets("Sheet2").Range("A2").Resize(Worksheets("Sheet2").Range("A2").CurrentRegion.Rows.Count - 1).Rows
    If rng2(1).Value = rng1(1).Value Then
      rng1.EntireRow.Copy
      rng2.EntireRow.PasteSpecial (xlPasteValues)
    End If
  Next rng2
Next rng1

End Sub
0
votes

This snippet may help you (warning: written without any testing )

Dim RowCollection As New Collection

Dim rgRow1 As Range
For Each rgRow1 In RangeFromSheet1
    ' saves each sheet1 row indexed by the (string) value of the 1st cell
    Call RowCollection.Add(rgRow, CStr(rgRow1.Cells(1, 1).Value))
Next rgRow1

Dim rgRow2 As Range
For Each rgRow2 In RangeFromSheet2
    ' try to find matching row
    On Error Resume Next
    Set rgRow1 = Nothing
    Set rgRow1 = RowCollection(CStr(rgRow2.Cells(1, 1).Value)) ' lookup using sheet2 val
    On Error GoTo 0
    If Not rgRow1 Is Nothing Then
        rgRow2.Value = rgRow1.Value ' found a match, so copy values
    End If
Next rgRow2

note: RowCollection.Add will fail on duplicate key values - so if that's a possibility you'll need to add some extra checking

0
votes

Use this :

Sheet2.Select (Sheet1.Rows(index).Copy)     // Index is copy row index in sheet1

Sheet2.Paste (Rows(index))       // Index is Paste row index in sheet2