Create a Macro that help paste the lot#

[Lawrence]

Hi all Gurus,

I have 3 worksheets namely

1) Receiving Unit by Lot# worsheet
2) Shipout unit by Lot# worksheet
3) XXX worksheet

All 3 worksheets contain 2 column titles

Lot #
Total Quantity

i want to create a Macro that help paste the lot#, not found in "Receiving
unit worsheet" but found in "Shipout unit worksheet" in "XXX worksheet".

Thanks
Lawrence

 

[Joel]

I made the ID column A. Change as required


Sub FindMissingData()

Const ID_Col = "A"

Set RcvSht = Sheets("Receiving Unit by Lot#")
Set ShipSht = Sheets("Shipout unit by Lot#")
Set TmpSht = Sheets("XXX")

TmpSht.Cells.ClearContents
'copy header row from Shipped shat to Temp Sht
ShipSht.Rows(1).Copy _
Destination:=TmpSht.Rows(1)


TmpRow = 2
RowCount = 2
With ShipSht
Do While .Range(ID_Col & RowCount) <> ""
ID = .Range(ID_Col & RowCount)

Set c = RcvSht.Columns(ID_Col).Find(what:=ID, _
LookIn:=xlValues, lookat:=xlWhole)

If c Is Nothing Then
.Rows(c.Row).Copy _
Destination:=TmpSht.Rows(TmpRow)

TmpRow = TmpRow + 1
End If

RowCount = RowCount + 1
Loop


End With


End Sub