I didn't create a temporary sheet. Instead I opened the workbook with new
PO's in ReadOnly mode. Because you can multiple rows with the New PO I put
an X in column IV when a new PO number was found. Then used Autofilter to
filter the Rows with X's. I only copied the visible rows (the ones with X's).

I asumed there was a header row in both workbooks. I put in a dialog box to
let the user select the workbook to open.


Sub GetNewPOs()

Set OldSht = ThisWorkbook.Sheets(1)
LastRow = OldSht.Range("A" & Rows.Count).End(xlUp).Row
NewRow = LastRow + 1

FileToOpen = Application _
.GetOpenFilename(filefilter:="Excel Files (*.xls), *.xls", _
Title:="select workbook with new Po's")
If FileToOpen = False Then
MsgBox ("Cannot Open file - Exiting Macro")
Exit Sub
End If

Set Newbk = Workbooks.Open(Filename:=FileToOpen, ReadOnly:=True)

With Newbk.Sheets(1)
RowCount = 2
Do While .Range("D" & RowCount) < ""
PO = .Range("D" & RowCount)

With OldSht
'check if PO exists
Set c = .Columns("D").Find(what:=PO, _
LookIn:=xlValues, lookat:=xlWhole)

If c Is Nothing Then
'put X in column IV if PO should be moved to old workbook
.Range("IV" & RowCount) = "X"

End If
End With
RowCount = RowCount + 1
Loop

'copy rows with X's in column IV
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
.Columns("IV:IV").AutoFilter
.Columns("IV:IV").AutoFilter Field:=1, Criteria1:="X"
Set NewPOs = .Rows("2:" & LastRow).SpecialCells(xlCellTypeVisible)
NewPO.Copy Destination:=OldSht.Rows(NewRow)
'delete the X's in Column IV
OldSht.Columns("IV").Delete
End With

Newbk.Close savechanges:=False
End Sub


"jwags" wrote:

Joel,

Thank you very much for your interest to help me out. Here are the
answers to your questions.

1. I would like the macro to go into an existing workbook. The
worksheets name will be Supplier Requirements

2. What I can do is copy and paste the newly downloaded worksheet into
the existing workbook. The worksheet
name will be Supplier Requirements and Sheet1 (as it is only
temporary).

3. PO is a combination of Letters and Numbers and is located in column
D. There can be multiple lines with the
same PO number in it. Is that going to pose a problem? For
example, Row 2 and 3 could be for the same PO
just different line items.

4. New PO's can be put at the end of the list and should not be
colored.

5. The duplicates should not be copied over...just the new PO's that
are not in the master listing.

I really do appreciate your assistance with this. Thanks!