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