Extracting data from one sheet unless it is also on a second works
 

hi

Sheet1 of my workbook has data under columns EmpID, LastName, FirstName, dob.

Sheet2 has the same structure, but with different data. But, there is
overlap - some of the data is found in both sheets.

Here is my problem: I want data on Sheet3 (also with the same structure) to
show ALL data rows from Sheet1 EXCEPT those found on Sheet2. I'm quite new
to Excel programming, so I'd really appreciate any clues as to how to do
this. There are enough data rows that I don't want to always have to do this
'by hand'.

much thanks
Victoria

Extracting data from one sheet unless it is also on a second works
 

Sub CopyUnique()

Set Sht1 = Sheets("Sheet1")
Set Sht2 = Sheets("Sheet2")
Set Sht3 = Sheets("Sheet3")

'set New row to the row hwre to copy on sheet 3
NewRow = 2

With Sht1
RowCount = 2
Do While .Range("A" & RowCount) < ""
EmpID = .Range("A" & RowCount)

With Sht2
Set c = .Columns("A").Find(what:=EmpID, _
LookIn:=xlValues, lookat:=xlWhole)
End With

If c Is Nothing Then
.Rows(RowCount).Copy _
Destination:=Sht3.Rows(NewRow)
NewRow = NewRow + 1

End If

RowCount = RowCount + 1
Loop
End With
End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=157530

Microsoft Office Help

Extracting data from one sheet unless it is also on a second w
 

Joel - thanks very much. Works like a charm!
Victoria

"joel" wrote:


Sub CopyUnique()

Set Sht1 = Sheets("Sheet1")
Set Sht2 = Sheets("Sheet2")
Set Sht3 = Sheets("Sheet3")

'set New row to the row hwre to copy on sheet 3
NewRow = 2

With Sht1
RowCount = 2
Do While .Range("A" & RowCount) < ""
EmpID = .Range("A" & RowCount)

With Sht2
Set c = .Columns("A").Find(what:=EmpID, _
LookIn:=xlValues, lookat:=xlWhole)
End With

If c Is Nothing Then
.Rows(RowCount).Copy _
Destination:=Sht3.Rows(NewRow)
NewRow = NewRow + 1

End If

RowCount = RowCount + 1
Loop
End With
End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=157530

Microsoft Office Help

.