Assuming the workbook where the data will be copied to is the activeworkbook
when you run the macro in accordance with your description. (makes no
assumption about which workbook contains the macro).

Dim bk as Workbook, sName as String
Dim bk1 as Workbook
set bk1 = ActiveWorkbook
sName = worksheets("M").Range("A1")
if sname = "" then
msgbox "No name specified for the workbook"
application.Goto bk1.Worksheets("M").Range("A1"), True
exit sub
end if
set bk = Workbooks.Open( Filename:= _
"\\w2k6001\shared\CSDGAPP\miTeam\Manpower\AManpowe rhcv0.2.xls")
bk.Worksheets("ASC").Range("D7:Q106").copy _
Destination:=bk1.Worksheets(ASC).Range("D7")
application.displayAlerts = False
bk1.SaveAs bk1.Path & "\" & sName
application.DispalyAlerts = True
bk.close Savechanges:=False


--
Regards,
Tom Ogilvy

" wrote:

Thanks again for your response Tom, sorry about this i'm not very good
at explaining what i'm trying to do. I think we're going down the right
path though. So, i've got a fixed workbook that is updated by someone
else. Im trying to get the data off that into my sheet. Their file is
the one located under \\w2k600\shared\csdgapp\miteam\Manpower and is
called AManpowerhcv0.2.xls. I'm trying to copy off the sheet in that
file called ASC to a file on my sheet called ASC also, but my sheet
will have a dynamic filename created depending on what is contained in
cell A1, sheet M of my file. Does this help at all?

I think the process should go something like this:
User opens my file from previous week, which is named according to the
date it was runt he previous week (yymmdd_hc.xls)
User enters file name into cell A1 of sheet M to be used for this weeks
update (060315_hc.xls for today for example)
User runs macro which saves file as the name in cell a1, sheet M of my
file
Macro copies data from AManpowerhcv0.2.xls to 060315_hc.xls although
this will have to be referenced to call A1 on sheet M due to the name
changing each week

Thanks again for your help