My Best Guess at what you want is: (hard to know which book is being renamed
and which book contains M and which book contains ASC).

Dim bk as Workbook, sName as String
Chdir = "\\w2k6001\shared\csdgapp\miteam\Manpower"
Sheets("ASC").Select
sName = worksheets("M").Range("A1")
ActiveWorkbook.SaveAs sName
set bk = Workbooks.Open ( Filename:= _
"\\w2k6001\shared\CSDGAPP\miTeam\Manpower\AManpowe rhcv0.2.xls")
bk.Worksheets("ASC").Range("D7:Q106").copy _
Destination:=Workbooks(sName).Worksheets(1).Range( "D7")

--
Regards,
Tom Ogilvy

" wrote:

basically, what i'm going to do is insert the filename into cell a1 on
sheet m as hc060313.xls, the first thing my macro will do is save the
file to the name in cella1, followed by extracting data from another
sheet and pasting it into different sheets on hc060313.xls (or
whatever that document is saved as that week) - the code included is
just part of the pasting process.

so i guess my question is, can i get the macro to activate a sheet
based on what the name is in cella1?
and secondly while i'm here, how would i go about forcing the file to
save as the name in cell a1?

thanks for your help :)