Copy cells after comparison

fboyd

New Member
Joined
Aug 18, 2006
Messages
33
I have 2 sheets. I compare column B of sheet 1 with column A of sheet 2. Any cells in sheet 2 which are not in sheet 1 are copied to sheet 1. The code I am using copies the requested data.

Sub copy()
Dim s1 As Worksheet
Dim s2 As Worksheet
Set s1 = Worksheets("Working")
Set s2 = Worksheets("Daily Download")
lr2 = s2.Cells(Rows.Count, 1).End(xlUp).Row
lr1 = s1.Cells(Rows.Count, 1).End(xlUp).Row
For Each cell In s2.Range("A1:A" & lr2)
If IsError(Application.Match(cell, s1.Range("B1:B" & lr1), 0)) Then
cell.copy Destination:=s1.Range("B" & lr1).Offset(8)
lr1 = lr1 + 1
End If
Next cell
End Sub

I want to take this a bit further and copy additional cells in the same row.
For example.
Sheet1
B1
George

Sheet2
A1 C1 E1 J1 K1, L1, M1, N1, O1
John Smith Lima Ohio 10 aa ss bb to

Sheet 1 - B1 & Sheet 2 - A1 do not match so I need to copy the rest of the cells of row 1 to Sheet 1.
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Watch MrExcel Video

Forum statistics

Threads
1,133,324
Messages
5,658,155
Members
418,430
Latest member
Kizega

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top