transfering data

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

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(xlup)(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" <pisanichris.1wym6a_1129406703.1422@excelforum-nospam.com>
wrote in message
news:pisanichris.1wym6a_1129406703.1422@excelforum-nospam.com...
>
> 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
>