Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merging the Changes from One Excel Document into another
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merging the Changes from One Excel Document into another
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" wrote: 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merging the Changes from One Excel Document into another
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! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merging the Changes from One Excel Document into another
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! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merging the Changes from One Excel Document into another
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merging the Changes from One Excel Document into another
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. "jwags" wrote: 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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merging the Changes from One Excel Document into another
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" wrote: 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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merging the Changes from One Excel Document into another
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. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merging the Changes from One Excel Document into another
I left off an S in the variable name
from: NewPO.Copy Destination:=OldSht.Rows(NewRow) to NewPOs.Copy Destination:=OldSht.Rows(NewRow) "jwags" wrote: 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. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merging the Changes from One Excel Document into another
Wonderful....works great! I really do appreciate your help with this,
going above and beyond what you needed to. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Merging cell data into a document? | Excel Discussion (Misc queries) | |||
Document merging function | Excel Discussion (Misc queries) | |||
Dollar amounts aren't merging from Excel into a Word document... | Excel Discussion (Misc queries) | |||
Merging Document Help | Excel Worksheet Functions | |||
How to put pictures into an excel document for merging in Word? | Excel Worksheet Functions |