Merging the Changes from One Excel Document into another

[jwags]

I am trying to maintain a master spreadsheet of a list of PO's
received. This list is downloaded from a website into Excel. I color
code each entry dependent on many different things. Each week I
download the spreadsheet and would like to merge the differences on
the new one with the already color coded entries from the week
before. Is there a easy and efficent way to do this automatically?

 

[Joel]

You probably need a simple macro. Answer the following questions and I will
write the macro

1) Where do you want the macro to go. You can place the macro in its own
workbook or put it in a master workbook where the old worksheet is located.
then add a new worksheet every tiome you download.
2) How are the worksheets arranged. Are both the new and old worksheets in
the same workbook? What are the names or the worksheets?

3) What is the column where the PO is located?

4) Do you wnat neww PO's to be put at the end of the List? Do you want
these new lines colored?

5) The Duplicate PO's do you want the new data to over-write the old data
just keep the formating like the colors or do something different?

 

[jwags]

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!

 

[Joel]

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]

Thank you very much for the script. I pasted it into the workbook and
was able to get it working until the line .Columns
("IV:IV").AutoFilter. I get an run time error message at this point
and it goes no further. Is it a problem with the way I have it
setup? I looked in column IV and there are no X's in the column.

 

[Joel]

The error occured because there were no missing PO's. I can add a check to
make sure there is no differences to avoid the error.

I'm not sure if my code has a problem or you are opening two workbooks with
the same number of PO's. Try making the two workbooks different so it finds
some differences.

What happens if additional line items get added to a PO. My code isn't
checking for this condition. What column is the Line items. Do you also
want me to check line items as well as PO's.

 

[Joel]

I found the problem. The x's where going into the wrong workbook. I added a
check to give warning message if no differences were found

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)
End With

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

End If

RowCount = RowCount + 1
Loop

'copy rows with X's in column IV
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
'check if there are x's to prevent errors
Set c = .Columns("IV:IV").Find(what:="X", _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
MsgBox ("No difference found in new workbook")
Else
.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 If
End With

Newbk.Close savechanges:=False
End Sub

 

[jwags]

I have one last error. The line NewPO.Copy Destination:=OldSht.Rows
(NewRow) gives me an error. The worksheet shows the two new line
items but seems to be unable to copy them.

 

[Joel]

I left off an S in the variable name
from:
NewPO.Copy Destination:=OldSht.Rows(NewRow)
to
NewPOs.Copy Destination:=OldSht.Rows(NewRow)

 

[jwags]

Wonderful....works great! I really do appreciate your help with this,
going above and beyond what you needed to.