Auto Erase file's Internal Data

[mmbhatti]

Hello Excel Experts...
In my office, we have some excel files which re-used every year to collect and feed new data for the year. For this what we do is, make a copy of previously used Data file, delete previous entered data (except the rows/columns where formula exist), changes it's name and then save it with new year's name. In this way we make a fresh file for new use.
Now i want to manage it in vba code.
Is it possible that when make a copy of excel file, vba do the work in this way
First it check whether name of this file is changed, if yes, it ask for if I want to erase internal data or not. And do the work as we recommend.
...
I hope you understand my question...

 

[BrianM45]

Good morning,

Just name the files with the year of work, then with VBA, when you change the year, you can easily open the old files from year N-1, rename them to year N and erase the data.

That said, the best would be to have model files to use each year by copying them and renaming them

Good Sunday

 

[mmbhatti]

Good morning,

Just name the files with the year of work, then with VBA, when you change the year, you can easily open the old files from year N-1, rename them to year N and erase the data.

That said, the best would be to have model files to use each year by copying them and renaming them

Good Sunday

I am newbie in VBA, can't write these codes...
What will be VBA for this...

 

[BrianM45]

I have to go, but in the "Developer" toolbar you have the macro recorder to start helping you

 

[mmbhatti]

I have to go, but in the "Developer" toolbar you have the macro recorder to start helping you

Okk..
Thank you for your time Brian.

 

[Skyybot]

Another solution would be to remove the data on the file (like you're starting a new year) and then save it as a Template. Call it Data Template, or something. That way you have a blank Template to use each year.

 

[mmbhatti]

Another solution would be to remove the data on the file (like you're starting a new year) and then save it as a Template. Call it Data Template, or something. That way you have a blank Template to use each year.

I want to make this as like a tricky file..
When someone make copy of it and change its name, at first opening it should ask for if user want to continue from previous saved data or need a fresh file for fresh use.

 

[Skyybot]

I want to make this as like a tricky file..
When someone make copy of it and change its name, at first opening it should ask for if user want to continue from previous saved data or need a fresh file for fresh use.

No problem. You can save as a Macro Enabled Template. Then create a macro that changes file name on open with VBA.