Automated Excel File Creation with VBA

**msiyab** · 07-03-2013, 03:19 PM

Hi Guys,

I would like to know if its possible to create a command button to create a new excel file.
I have a voucher generator excel file, which generates vouchers on each sheet. So at the end of each month, i would like to create a new excel file for the next month with just the first two sheets of the 100+ worksheets in the new excel file. This would be helful as i have over 50 such files which need to be renamed and modified each month. It would be nice if the command button would ask me to name the file before creating it.

I would like to modify 3-4 cells in this workbook, but will mention them only if the above mentioned is possible.
I am attaching the template file for your reference.

Regards,
Siyab

**Admin** · 07-03-2013, 03:36 PM

Hi

Save the workbook as Excel Macro-Enabled Template with the required two sheets. Now for each month open the template (which opens a copy of the template) and save as workbook.

**msiyab** · 07-03-2013, 04:04 PM

Thanks for the easy solution. I can use this for different files I have. But not for the 50+ files which needs modifications each month.

Let me explain the modifications. P.S. I am giving the example of what changes i require while changing from June 2013 to July 2013.
'Summary' sheet changes:
- A1 changes to "GENERAL MAINTENANCE VOUCHER SUMMARY - JULY 2013" from JUNE 2013 and so on each month.
- A3:A150 need to replace -06- with -07- (06 = June & 07 = July). So the July 2013 file will have GM-07-0001 and so on.

'Format' sheet changes:
- By the end of each month, cell J8 will have reached GM-06-0100+/-. SO when the new excel is generated, the value in J8 should be replaced to GM-07-0000 (not 0001)

Now, you see I have to do this manually for 50+ files each month. So any suggestion or help will be highly appreciated.

**Admin** · 07-03-2013, 10:39 PM

Hi

Please find attached the Template. I hope this will find useful.

**msiyab** · 07-03-2013, 10:45 PM

Wow!! Thats just amazing work.

I tried 2-3 files from home and it worked really good. Will go to office tomorrow and check this with other files.

Thanks again.

**Admin** · 07-03-2013, 10:48 PM

You are welcome. Glad you liked it

**msiyab** · 07-03-2013, 10:57 PM

Sorry to be a trouble to you!!
But theres a teensy-weensy problem with the file.

When you enter the wrong date and the error screen appears "Do you want to try again?". If i click Ok, the "workbook closing" error appears and closes the workbook

Help please!