Auto-generate new weekly workbook based on a saved template

Hi,

I would really grateful if someone could help with the issue I am having with Excel 07.

I have created and saved a template in excel 07 that contains macros for sorting data, and vlookups for analysing the data. This data is added daily.

The template has 5 tabs for data for each day of the week, and a sixth tab for analysing that weekly data.

Is there a bit of code that could be added to the template that auto-generates a new workbook based on the above each week, with all the macros etc in template, minus the data.

If someone could advise how that is possible it would be much appreciated.

thanks,

James

James,

Welcome to the forum.

What you're requesting can be accomplished, the code would just need to know what fields need clearing, where to save the new workbook (if different from the current workbook's folder) and what to name the new workbook. The code would look something like this:

Please Login or Register  to view this content.

Hope that helps,
~tigeravatar

an easier way to erase you old data would be to name your data range for each sheets and perform a ClearContents for each of those ranges.
You can manage to create a dynamic range name that expand with the data you input.

that would replace the first part of the macro suggested by Tiger.

The rest of the macro Tiger wrote is still valid.
Pierre

Please Login or Register  to view this content.

an even simpler solution is to keep a BLANK workbook in a directory and open it each time you need to start a new week. Don't forget to change the name of the file the first time you save it. You could protect the BLANK workbook so you wont mess with it or make it read only. This will force you to save it with a new name.
Pierre

Guys,

thanks so much for your help, I wasnt expecting such a speedy response, much appreciated.

James