how to get a macro to work with any work book name

i have a made a macro that copies info to a new sheet now that is working great but if i change the name of the work book it wont work any more so i need the macro to work with what ever name i give the workbook

the current name is
AVERAGE PRICE (update 2009) Mimmos Armico 170809.xls

i have attached the code in note pad

thank you
wade

Which workbook contains the code, and which one is active when you start running the code?

both work books are the same. one is a copy of the other but with a different name

AVERAGE PRICE (update 2009) Mimmos Armico 170809.xls is the work book i am copying from and AVERAGE PRICE (update 2009) Mimmos Armico CLEAN.xls is the one i am copying to

the first ones name will change week to week the second one will be come the new weeks book and the 1st one will be archived as a report

so for you to understand
AVERAGE PRICE (update 2009) Mimmos Armico 170809.xls
will open a work book called AVERAGE PRICE (update 2009) Mimmos Armico CLEAN.xls and then copy values to it .i will then SAVE AS AVERAGE PRICE (update 2009) Mimmos Armico 240809.xls for the next week

and then do the same again but this time the fist one would be
AVERAGE PRICE (update 2009) Mimmos Armico 240809.xls

and so on

so in the code it needs to use the current work book name that i run the macro from .
thank you i hope this helps

That doesn't actually answer the question about which is active at the start, but my best guess is you want something like this (I removed a lot of unnecessary code):

Please Login or Register  to view this content.

Set wbkClean = Workbooks("AVERAGE PRICE (update 2009) Mimmos Armico CLEAN.xls")

is giving me a runtime erroe
script out of range

the work book that is active first is AVERAGE PRICE (update 2009) Mimmos Armico 170809.xls

Oops - I should have guessed what the 'openbook' macro does! Need to change the order a bit:

Please Login or Register  to view this content.

MAN ALIVE

I COULD KISS YOU

THANK YOU SO MUCH
YOU HAVE HELPED ME FINALIZE 5 MONTHS OF WORK

thank you thank you thank you thank you thank you thank you thank you thank you thank you thank you thank you thank you thank you thank you thank you thank you thank you thank you thank you thank you thank you thank you thank you thank you thank you thank you thank you

You're welcome welcome welcome etc.