Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding data in another Excel file
Hi everyone,
I'm working on a macro that copies data from one Excel file to another. Excel file 1, called Calcs.xls, contains calculations and placed orders on certain products. Excel file 2, called Incoming.xls, contains information when the products are supposed to arrive. The Incoming.xls contains one sheet for every week of the year, that's the reason for it not beeing in the same file as the other data - which in turn change content over the course of the year. What I need to do is something similar to the Lookup functions in Excel. Incoming.xls needs to check cell C1 (contains the current weeknumber), go to the sheet "Orders" in Calcs.xls, and find the Incoming.xls-C1 value on a row 3. Once that's found, it needs to copy the contents of that column (rows 5 - 40) to the Incoming file. I've tried a couple of different variations, but can't figure out how to solve this - maybe because I'm still fairly new at VBA coding? :) Anyone who cares to help me out here? Regards, Christian Davidsson |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding data in another Excel file
Set rng = workbooks("Calcs.xls").Worksheets("Orders").Rows(3 ).Cells set rng1 = Workbooks("Incoming.xls").Worksheets(1).Range("C1" ) ' find value of C1 in Row(3) of Orders sheet in calcs.xls for each cell in rng if cell.Value = rng1.Value then ' value found, copy rows 5 - 40 of this copy to ??? cell.offset(2,0).Resize(36,1).Copy Destination:=rng1.offset(4,0) exit for end if Next You left out a lot of information like what sheet the C1 value is on and where you want the data copied to in incoming.xls. The above should get you started. -- Regards, Tom Ogilvy "Christian Davidsson" wrote in message ... Hi everyone, I'm working on a macro that copies data from one Excel file to another. Excel file 1, called Calcs.xls, contains calculations and placed orders on certain products. Excel file 2, called Incoming.xls, contains information when the products are supposed to arrive. The Incoming.xls contains one sheet for every week of the year, that's the reason for it not beeing in the same file as the other data - which in turn change content over the course of the year. What I need to do is something similar to the Lookup functions in Excel. Incoming.xls needs to check cell C1 (contains the current weeknumber), go to the sheet "Orders" in Calcs.xls, and find the Incoming.xls-C1 value on a row 3. Once that's found, it needs to copy the contents of that column (rows 5 - 40) to the Incoming file. I've tried a couple of different variations, but can't figure out how to solve this - maybe because I'm still fairly new at VBA coding? :) Anyone who cares to help me out here? Regards, Christian Davidsson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding data in another Excel file
Hi Tom, and thanks for responding!
Sorry for leaving out those parts of information you mention - guess I was so stuck on my problem, I forgot to see everything when I wrote the question. Your solution works perfect, just the way I wanted it to. Thank you very much! However, after beeing implemented for two minutes, the human error comes in. The people handling Orderx.xls thought they'd add a new item, and did so in the middle of the existing products - thus increasing the span needed to be copied. This adding/removing of items requiers the people on order to communicate with the people handling the Incoming.xls file, any time the change it. And since they won't do that (they didn't this time anyway) - is it possible to do a 2'nd lookup using a ItemNo. on the very left column and thus copying the data in the correct order? Say if Order.xls contains: Item1 - 1234 Item2 - 5678 Item3 - 1234 Item4 - 5678 And thus the result in Incoming.xls would be: Item1 - 1234 Item2 - 5678 Item4 - 5678 Item3 - 1234 PS. The C1 value is on every sheet in Incoming.xls, showing the current week for that sheet. The destination for the copy is the upcoming week. I've created a macro that checks what the last week in the file is (based on the C1 value and the sheet names), it then adds a sheet, formats it and enters the last C1 value + 1. Maybe not the best solution out there - but it works :) Regards, Christian Davidsson "Tom Ogilvy" skrev i meddelandet ... Set rng = workbooks("Calcs.xls").Worksheets("Orders").Rows(3 ).Cells set rng1 = Workbooks("Incoming.xls").Worksheets(1).Range("C1" ) ' find value of C1 in Row(3) of Orders sheet in calcs.xls for each cell in rng if cell.Value = rng1.Value then ' value found, copy rows 5 - 40 of this copy to ??? cell.offset(2,0).Resize(36,1).Copy Destination:=rng1.offset(4,0) exit for end if Next You left out a lot of information like what sheet the C1 value is on and where you want the data copied to in incoming.xls. The above should get you started. -- Regards, Tom Ogilvy "Christian Davidsson" wrote in message ... Hi everyone, I'm working on a macro that copies data from one Excel file to another. Excel file 1, called Calcs.xls, contains calculations and placed orders on certain products. Excel file 2, called Incoming.xls, contains information when the products are supposed to arrive. The Incoming.xls contains one sheet for every week of the year, that's the reason for it not beeing in the same file as the other data - which in turn change content over the course of the year. What I need to do is something similar to the Lookup functions in Excel. Incoming.xls needs to check cell C1 (contains the current weeknumber), go to the sheet "Orders" in Calcs.xls, and find the Incoming.xls-C1 value on a row 3. Once that's found, it needs to copy the contents of that column (rows 5 - 40) to the Incoming file. I've tried a couple of different variations, but can't figure out how to solve this - maybe because I'm still fairly new at VBA coding? :) Anyone who cares to help me out here? Regards, Christian Davidsson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding data in another Excel file
I would really need to have a better understanding of what is going on to do
a second lookup and copy in the proper order. If you are just saying rows 5 to 40 could be dynamic, then I can find the last value in the column and copy from 5 to the Set rng = workbooks("Calcs.xls").Worksheets("Orders").Rows(3 ).Cells set rng1 = Workbooks("Incoming.xls").Worksheets(1).Range("C1" ) ' find value of C1 in Row(3) of Orders sheet in calcs.xls for each cell in rng if cell.Value = rng1.Value then ' value found, copy rows 5 - 40 of this copy to ??? set rng2 = cell.parent.range(cell.offset(2,0), _ cell.parent.cells(rows.count,cell.column).End(xlup )) rng2.Copy Destination:=rng1.offset(4,0) exit for end if Next That assumes that any data below row 5 in that column needs to be copied. would that work? If not, you would have to give much more detail on where to look for item no, how it relates to information in the column where the value for row 3 is found and so forth. -- Regards, Tom Ogilvy "Christian Davidsson" wrote in message ... Hi Tom, and thanks for responding! Sorry for leaving out those parts of information you mention - guess I was so stuck on my problem, I forgot to see everything when I wrote the question. Your solution works perfect, just the way I wanted it to. Thank you very much! However, after beeing implemented for two minutes, the human error comes in. The people handling Orderx.xls thought they'd add a new item, and did so in the middle of the existing products - thus increasing the span needed to be copied. This adding/removing of items requiers the people on order to communicate with the people handling the Incoming.xls file, any time the change it. And since they won't do that (they didn't this time anyway) - is it possible to do a 2'nd lookup using a ItemNo. on the very left column and thus copying the data in the correct order? Say if Order.xls contains: Item1 - 1234 Item2 - 5678 Item3 - 1234 Item4 - 5678 And thus the result in Incoming.xls would be: Item1 - 1234 Item2 - 5678 Item4 - 5678 Item3 - 1234 PS. The C1 value is on every sheet in Incoming.xls, showing the current week for that sheet. The destination for the copy is the upcoming week. I've created a macro that checks what the last week in the file is (based on the C1 value and the sheet names), it then adds a sheet, formats it and enters the last C1 value + 1. Maybe not the best solution out there - but it works :) Regards, Christian Davidsson "Tom Ogilvy" skrev i meddelandet ... Set rng = workbooks("Calcs.xls").Worksheets("Orders").Rows(3 ).Cells set rng1 = Workbooks("Incoming.xls").Worksheets(1).Range("C1" ) ' find value of C1 in Row(3) of Orders sheet in calcs.xls for each cell in rng if cell.Value = rng1.Value then ' value found, copy rows 5 - 40 of this copy to ??? cell.offset(2,0).Resize(36,1).Copy Destination:=rng1.offset(4,0) exit for end if Next You left out a lot of information like what sheet the C1 value is on and where you want the data copied to in incoming.xls. The above should get you started. -- Regards, Tom Ogilvy "Christian Davidsson" wrote in message ... Hi everyone, I'm working on a macro that copies data from one Excel file to another. Excel file 1, called Calcs.xls, contains calculations and placed orders on certain products. Excel file 2, called Incoming.xls, contains information when the products are supposed to arrive. The Incoming.xls contains one sheet for every week of the year, that's the reason for it not beeing in the same file as the other data - which in turn change content over the course of the year. What I need to do is something similar to the Lookup functions in Excel. Incoming.xls needs to check cell C1 (contains the current weeknumber), go to the sheet "Orders" in Calcs.xls, and find the Incoming.xls-C1 value on a row 3. Once that's found, it needs to copy the contents of that column (rows 5 - 40) to the Incoming file. I've tried a couple of different variations, but can't figure out how to solve this - maybe because I'm still fairly new at VBA coding? :) Anyone who cares to help me out here? Regards, Christian Davidsson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding total # of records in an Excel file without scrolling | Excel Discussion (Misc queries) | |||
exported Outlook contacts to an Excel file - finding unwanted ' sy | Excel Discussion (Misc queries) | |||
Finding Asymptotes from a set of data in Excel | Excel Worksheet Functions | |||
Finding Asymptotes from a set of data in Excel | Excel Discussion (Misc queries) | |||
Finding data in Excel | New Users to Excel |