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