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.
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,105,939
Messages
5,508,243
Members
408,670
Latest member
lhmwnrexcel

This Week's Hot Topics

Top