Macro to start a new identical excel file

[lfmemp]

Hello everyone,

I do have an excel file which needs to be used for data collection. I created a template which I give to my colleagues and who need to enter different data every day.
I would like to have a macro in my template in a form of a buttom, which they can click and automatically a new file, same as my template is created.

The name of the new file should be the same as the template, with only difference the date it was created.
For example if the file name was "Template Oct-17, 2012" the new file should have the name
"Template Oct-18, 2012" and so on.
In this way, my colleagues can enter the data of a new day in that new created file.

Can somebody help me with the macro?

Thank you

[JBeaucaire]

I'm not sure you need a macro for this, an ACTUAL Excel Template does not overwrite itself, always required the user to save with a new unique name.

In Excel 2010, you save the file an Excel Tempalte (*.xlst)

Then place a useful "link" somewhere handy to that .xlst file, and click the link anytime you want a new file. Excel sees it is a template and opens a COPY of the template, it does not open the actual template when you "link to open". To actually open the original template to edit it, you would have to use Excel's File > Open from the main menu bar.


So, save as .xlst, create a shortcut link to call the template, and you're ready to go.

[patel45]

If you absolutely want a macro

Please Login or Register  to view this content.

[lfmemp]

Thank you for the input so far.
I would also like the macro to actually generate an identical file as the one I have, but in the same time delete all the input data. In other words, the file created should be identical as the first one (all tabs, graphs etc.) but the input data on Sheet1 (in form of a table) should appear blank.
In this way, the person who runs the macro will get an empty input page, where he can start entering new data.

Thank you

[lfmemp]

Also some more input...
@JBeaucaire, by template I do not mean an actual excel template. Sorry for that, probably my expression was not clear.

What I mean by template is a file I have created, into which I enter my relevant information. For example my 'template' has an input tab (Sheet_Input), where other users need to enter daily some data (in a table form). They do enter data in 2 specific columns (column A and C) and the rest of the columns I have set up to do some calculations. My 'template' has also other tabs, which show graphs etc. for which the data is pulled from the Sheet_Input, but users do not enter any data here. All data is pulled from the Sheet_Input tab.
What I now need is a macro that will create an identical file as my 'template' with all sheets, like the Sheet_Input, graph sheet etc and in which file the cells the users are entering data into are cleared.
I hope that clarifies.
Thanks

[JBeaucaire]

Your last post doesn't change my original suggestion. You're using this workbook to do some work, then you want to save it, and clear it to start again entering new data. IMO you're doing this the wrong way. You need to save your main workbook as an actual Excel template.

Then create a handy shortcut to that template.

Then use that shortcut to open a new document, clean and pristine for users to enter data into. The new doc has no filename so Excel will force you to save it with a new filename.

Want to start a new one? Click the shortcut again and bingo, done, another pristine copy is open. You could even have multiple workbooks opened at the same time with this approach.

Nothing you've said so far leads me to believe you actually need a macro, you need a real template.

[shg]

+1 for JB's suggestion.

[patel45]

Also some more input...
@JBeaucaire, by template I do not mean an actual excel template. Sorry for that, probably my expression was not clear.

What I mean by template is a file I have created, into which I enter my relevant information. For example my 'template' has an input tab (Sheet_Input), where other users need to enter daily some data (in a table form). They do enter data in 2 specific columns (column A and C) and the rest of the columns I have set up to do some calculations. My 'template' has also other tabs, which show graphs etc. for which the data is pulled from the Sheet_Input, but users do not enter any data here. All data is pulled from the Sheet_Input tab.
What I now need is a macro that will create an identical file as my 'template' with all sheets, like the Sheet_Input, graph sheet etc and in which file the cells the users are entering data into are cleared.
I hope that clarifies.
Thanks

I agree with JBeaucaire, but if you want macro, you have to define the range used for data input, then we can clear this range before save as.