I need to transfer data from one sheet to another every week. The first
sheet will contain a roster and the second sheet will contain the
values/totals of the roster from throughtout the year.

Is it possible to make a formula that will lookup the cell "week
number" from the roster sheet (that changes every week) and pastes the
data under the cell that contains the name week number in the datasheet
(the sheet that contains the data from previos weeks.

Therefore, it will copy from the current sheet and store them after
each other in a different sheet.

Please help,
Thanks,
Chris


--
pisanichris
------------------------------------------------------------------------
pisanichris's Profile: http://www.excelforum.com/member.php...o&userid=28138
View this thread: http://www.excelforum.com/showthread...hreadid=476531

If it is multiple lines, then you can use Data=Filter=Autofilter

Select the week number from the drop down, then copy that data.

You can get the code by turning on the macro recorder and performing the
action manually.

You can find the destination by

set cell = Worksheets("DataSheet").Cells(rows.count,1).End(xl up)(2)

then you just use

with Worksheets("Roster").Autofilter.Range
set rng = .offset(1,0).Resize(.Rows.count-1)
End With
rng.Copy Destination:=cell

--
Regards
Tom Ogilvy

"pisanichris"
wrote in message
...

I need to transfer data from one sheet to another every week. The first
sheet will contain a roster and the second sheet will contain the
values/totals of the roster from throughtout the year.

Is it possible to make a formula that will lookup the cell "week
number" from the roster sheet (that changes every week) and pastes the
data under the cell that contains the name week number in the datasheet
(the sheet that contains the data from previos weeks.

Therefore, it will copy from the current sheet and store them after
each other in a different sheet.

Please help,
Thanks,
Chris


--
pisanichris
------------------------------------------------------------------------
pisanichris's Profile:
http://www.excelforum.com/member.php...o&userid=28138
View this thread: http://www.excelforum.com/showthread...hreadid=476531