Compare two worksheets, find first match and paste, then delete source row

Question

I have two worksheets and want to paste two cells from the second worksheet (Sheet1) when find the first match in active sheet. Then delete the source row in Sheet1. I can't loop from the last row of the active sheet because I want to populate the first row from the top that matches. I'm also struggling to activate Sheet1 in order to delete the row:

Sub moveRecords()
Dim i, j As Long
With ActiveSheet
    'need to work down in active sheet in order to populate the first match with cells 1 & 2
    For i = 2 To 100
        For j = 2 To 1000
           If Cells(n, 1).Value = Sheets("Sheet1").Cells(j, 1).Value _
           And Cells(n, 2).Value = Sheets("Sheet1").Cells(j, 2).Value Then
               Cells(n, 7).Value = Sheets("Sheet1").Cells(j, 1).Value
               Cells(n, 8).Value = Sheets("Sheet1").Cells(j, 2).Value
           'need to delete the source row in Sheet1
           End If
        Next j
    Next n
End With
End Sub

Can you please clarify your requirement? Where is your source data that you are trying to find? It would be better, if you can provide pictorial representation of your requirement. — Raj, Mar 13 '15 at 01:59
Here's a pic of the end result that has brought populated columns 7 and 8 in sheet 1 from the data from sheet2 where there is a match. Then need to delete the source row in sheet2. My first time here so need to work out how to paste the pic. Be back soon — LuigiX, Mar 13 '15 at 07:24
It seems the add image doesn't play nicely with Chrome so I used IE. Only to find I am prevented from posting pics until I have a '10' reputation. Oh well I tried :( — LuigiX, Mar 13 '15 at 07:34
Edited code to update sheet1 to sheet2. Sheet1 is the active sheet and I want to pull the data from sheet2 where the two fields match *Sub If Cells(n, 1).Value = Sheets("Sheet2").Cells(j, 1).Value _ And Cells(n, 2).Value = Sheets("Sheet2").Cells(j, 2).Value Then Cells(n, 7).Value = Sheets("Sheet2").Cells(j, 1).Value Cells(n, 8).Value = Sheets("Sheet2").Cells(j, 2).Value 'need to delete the source row in Sheet2* — LuigiX, Mar 13 '15 at 08:25
Can you please explain what `i` and `n` are supposed to be? It looks to me like you never initialize or change `n` and that `i` is never used. — AnotherParker, Mar 14 '15 at 02:44

Ygor Yansz · Answer 1 · 2015-03-13T11:44:22.560

Here is a little different approach, since you want to delete the entire row it's hard to keep the right track of the i or j variable so this code makes all the copy and paste marking the rows you must delete and after that delete them all, i was kinda confused with what you asked but i think that's it =]

Sub moveRecords()

        For j = 2 To 100
           If Sheets("Sheet1").Cells(j, 1).Value = Sheets("Sheet2").Cells(j, 1).Value _
           And Sheets("Sheet1").Cells(j, 2).Value = Sheets("Sheet2").Cells(j, 2).Value Then
               Sheets("Sheet1").Cells(j, 7).Value = Sheets("Sheet2").Cells(j, 1).Value
               Sheets("Sheet1").Cells(j, 8).Value = Sheets("Sheet2").Cells(j, 2).Value
               Worksheets("Sheet2").Cells(j, 1) = "Delete"
           End If
        Next

        For i = 2 To 100
           If Worksheets("Sheet2").Cells(i, 1) = "Delete" Then
           Worksheets("Sheet2").Cells(i, 1).EntireRow.Delete
           i = i - 1
           End If
        Next

End Sub

score 0 · Answer 2 · answered Mar 14 '15 at 02:36

If your data on sheet1 and sheet2 looks like below:

enter image description here

Here's the solution:

Sub test()

Set ExcelApp = CreateObject("Excel.Application")
Set wb = ActiveWorkbook
Set ws = wb.Worksheets("Sheet1")
Set ws1 = wb.Worksheets("Sheet2")
Set Rng = ws.UsedRange
RowCount = Rng.Rows.Count
Set Rng1 = ws1.UsedRange
RowCount1 = Rng.Rows.Count
For n = 1 To RowCount
    For j = 1 To RowCount1
       If ws.Cells(n, 1).Value = ws1.Cells(j, 1).Value _
       And ws.Cells(n, 2).Value = ws1.Cells(j, 2).Value Then
           ws.Cells(n, 7).Value = ws1.Cells(j, 1).Value
           ws.Cells(n, 8).Value = ws1.Cells(j, 2).Value
           ws1.Cells(j, 1).EntireRow.Delete
        'To set the search to start from top row
        j = 0
       End If
    Next j
Next n


End Sub

Output will be:

enter image description here

Compare two worksheets, find first match and paste, then delete source row

2 Answers2