First of all, i wanted to thank you all in advance. I've actually been scrounging information off this
for quite some time. You've helped me overcome a vast number of problems i've encountered.
This one though, i
couldn't find anything on, and i thought it was about time i asked you directly
I'm not very good at explaining,
so bear with me.
I have 9 Excel workbooks. 1 of these is a Final sheet, the presentation sheet that get's printed
and given out. The other 8 are "raw data" sheets where i've done all the calculations and made the tables.
Data workbooks are given to me each month and dropped in a folder, overwriting the previous "raw data" workbooks. Trouble is,
these workbooks have a Month name in the filename. This month name is important and i cant remove it. Example:
Final.xls, 101MAY.xls, 102MAY.xls, 103MAY.xls, 104MAY.xls, 105MAY.xls, 106.MAY.xls, 107MAY.xls and 108MAY.xls.
(These would overwrite 101MAR.xls, 102MAR.xls, 103MAR.xls etc... Final.xls does not get replaced)
I've written a
macro in Final.xls that copies and pastes all the relevent information from each Raw Data sheet into the corresponding Tab in
Workbooks.Open Filename:= _
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
Edit: sorry, i should have mentioned, this is replecated 8 times, once for each raw data workbook.
As you can see,
this does a Copy/Paste of the entire sheet from one workbook, into the Final.xls. Trouble is, this macro would obviously not
work after i've overwritten the May files with June files (101JUN.xls etc).
What i thought i could do is make a
cell in Final.xls with a drop down box, with the months Jan - Dec inside. Then i could create a second macro, that will run a
Find and Replace inside the First macro, using whichever entry in the drop down box is selected to Find/Replace the file
names in the macro.
Drop down box; select June.
Run new macro
**New macro would need something like "If June, then use 101Jun.xls... If August then use 101Aug.xls**
New Macro finds/replaces 101MAY.xls with 101JUN.xls inside the old macro
Run old macro
Macro copies and pastes data from 101JUN.xls like before.
Does that make sense Really sorry i made it so long
winded. I'm terrible at explaining things. If anyone can help, i'd be so grateful. I can obviously just do a manual find and
replace in the macro, but i'm trying to make this task run as automatic as possible, with as little human interaction as i
Really greatful. thanks guys...