Excel VBA: Find parameter, copy found row to another Worksheet (pasting starting at specific cell)

Question

Having 2 Worksheets (UPDATED,CHANGES) each one have parameters in each column in variable order

UPDATED Worksheet has the columns:

Name / Value / Units

CHANGES Worksheet has the columns:

Status / Name / Value / Units

So first I search in CHANGES a row with the Status: CHANGE
Then I need to get the Name of that row
To find it in UPDATED and copy the whole Row
And copy it in the position after the column Status where the name was found

Each Name is unique but as I mentioned before has a variable position, my code so far:

Sub CopyRealChange()
Dim sh1 As Worksheet, sh2 As Worksheet
Dim lr As Long, r As Long, x As Long
Dim chng As Range
Set sh1 = ThisWorkbook.Worksheets("UPDATED")
Set sh2 = ThisWorkbook.Worksheets("CHANGES")

lr = sh2.Cells(Rows.Count, "A").End(xlUp).Row
x = 2
For r = 2 To lr
    If Range("A" & r).Value = "CHANGE" Then 'Evaluate the condition.
        'Sh2.Range("B" & x).Value = Sh1.Range("B" & r).Value 'Copy same Column location
        'FIND
        With Worksheets(2).Range("a1:a1000")
            Set chng = .Find(sh2.Range("B" & x).Value, LookIn:=xlValues)
            If chng Is Nothing Then
                sh1.Range(c).EntireRow.Copy Destination:=sh2.Range("B" & x)
            End If
        End With
        'FIND

    End If
    x = x + 1
Next r

End Sub

So thanks in advance for the help to resolve my problem

Concerns with the code show an error at this line (in the FIND)

sh1.Range(c).EntireRow.Copy Destination:=sh2.Range("B" & x)

You probably don't need VBA for this, but hey it's whatever is best for your case. I am just a big fan of "if you don't need vba don't do use it". (INDEX and MATCH work wonders) — chancea
@chancea As VLOOKUP, INDEX-MATCH work great with sorted data but with my list is constantly changed, deleted, updated and as far as my few knowledge go, it only use one parameter as reference to return the value, in my case, I use one to locate then get the value next to the one found and update the info based on a search in other sheet, but if you could provide an insight answer with my case, I would check it out :). — Zegad
@zegad Okay going through this, I believe its possible But I am actually confused on the functionality you want, you currently have a list of names on both sheets? If thats the case, I could write a formula that grabs the "CHANGE" rows but they would of course just go away as soon as that word "CHANGE" went away. What would make sense in my mind is if you wanted a list on the UPDATED sheet that ONLY had the "CHANGED" names. — chancea

peege peege · Accepted Answer · 2014-12-09T21:27:31

There are a few issues with your code.

You didn't declare with which worksheet the range you are searching for is located.

If Range("A" & r).Value = "CHANGE" Then

You declared the worksheets in the beginning then change how you reference them in the code.

Set sh2 = ThisWorkbook.Worksheets("CHANGES")

With Worksheets(2).Range("a1:a1000")

Here is what I've got for you: using simple loops checking to see if values match and move data.

Sub CopyRealChange()

Dim sh1 As Worksheet, sh2 As Worksheet
Dim tempName As String
Dim lastRow1 As Long
Dim lastRow2 As Long

Set sh1 = ActiveWorkbook.Worksheets("UPDATED")
Set sh2 = ActiveWorkbook.Worksheets("CHANGES")

    lastRow1 = sh1.Cells(Rows.Count, "A").End(xlUp).Row    'Get last row for both sheets
    lastRow2 = sh2.Cells(Rows.Count, "A").End(xlUp).Row    'because you are searching both

    For s2Row = 2 To lastRow2                              'Loop through "CHANGES"
        If sh2.Cells(s2Row, 1).Value = "CHANGE" Then
            tempName = sh2.Cells(s2Row, 2).Value           'extra step for understanding concept
                                                           'There is a match, so now
            For s1Row = 2 To lastRow1                      'Search through the other sheet
                If sh1.Cells(s1Row, 1).Value = tempName Then                  
                    sh2.Cells(s2Row, 3).Value = sh1.Cells(s1Row, 2).Value    'Copy Values
                    sh2.Cells(s2Row, 4).Value = sh1.Cells(s1Row, 3).Value    
                End If
            Next s1Row
        End If
    Next s2Row
End Sub

Excel VBA: Find parameter, copy found row to another Worksheet (pasting starting at specific cell)

1 Answers