Pass Variable in VB Code

Hello:

I am using VB Macro Code attached to paste certain range of data from closed excelworkbook named
"01_2013.xls" to current open excel file Called "2013_Yearly.xls", in sheet called "Jan13".

"2013_Yearly.xls will have sheet for all 12 months, Jan13,Feb13,Mar13.....Dec13

To do this I open 2013_Yealy.xls file and go to Jan13 tab and run the above macro and it works fine.

I will have monthly .xls sheet as "02_2013", "03_2013", etc

I want to see if it is possible to have the attached macro modified so that
when i open 2013_Yearly.xls file and go to let us say Feb13 tab to run the above macro.
Once i run the macro, the source file name in the code should automaticaly change to "02_2013.xls" for Feb and destination should change to Feb13 in the code
Basically i wan to use one macro to perform for all 12 months rather then creating macro for each month.

I hope this is clear and looking forward for quick help

As always thank you for your help in advance.

Riz

Hi Andrew:

Thank you so much for your quick help.
I need to ask one more favor.
The name of the closed workbook is not exactly MM_YYYY.xls
It is in format : MM_YYYY_StoreName.xls.
I have to do this for numerous store.
You could set store name variable eg
STORENAME="DQ_STORE1"
where i could cahnge STORENAME variable for each store.
I am sorry, i forgot to mentioned that earlier.
Can you please incorporate the above file name format so that i can try out the code.

Thank you

Riz

Hi Andrew:

I know i have competed this task with your great help.
I am writing another VB Macro for another scenerio where i would need to ask "Which Month, year MMMYY" (Example : Jan13)
Here i have sheet called "Inventory2013" in the same 2031_Yearly.xls file.
Cell F40:Q40 have heading as Jan13,Feb13,Mar13......Dec13
Once i have entered which month, Year then need to paste the data under the month (Row 41)

Here basically, i need the code for destination column.

Please help me get started this.

Thanks again

Riz

Based on post 8 in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

In future, to mark your thread as Solved, you can do the following -
Select Thread Tools-> Mark thread as Solved.

Incase your issue is not solved, you can undo it as follows -
Select Thread Tools-> Mark thread as Unsolved.