Finding data in another Excel file

  • Thread starter Thread starter Christian Davidsson
  • Start date Start date
C

Christian Davidsson

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
 
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.
 
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
 
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 there:

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.
 
Back
Top