Copying tabs to new workbook with Macro

[dramond1975]

Hi,

I have a spreadsheet with a data tab and 12 tabs for each month of the year. This spreadsheet is financial statements for each month. I created a Macro on the Dec tab to copy all the tabs and move it to a new work book for the next years financial statements and to pull forward the cumulative totals into the January statement on the new workbook.

My issue is when the new workbook is created and we reach the end of the year again the Macro button on the newly created workbook doesn't work again because it doesn't pull the Macro from the previous workbook, just the button. Is there a way I can get the original macro to pull thru on continuous workbooks for reports with multiple years?

[MatrixMan]

I assume you mean one of the modules didn't copy (because the code attached to the sheet being copied will move across, but the module doesn't belong to the sheet - it belongs to the workbook - so isn't copied).

Easiest way would be to create a copy of the entire workbook (to get the VBA modules, UserForms etc), add a dummy sheet (because you can't have no sheets in a workbook), delete all sheets but the dummy sheet, copy the source sheets into the workbook, then delete the dummy sheet.

Make sense?

[dramond1975]

Yes it does. I will try that.

Thanks

[Sintek]

Hi draymond

Perhaps another way is to use a Master workbook as your Financial Statement.
Create a macro within that does the following:
1. At year end, Save a copy to a folder as a pdf.
2. Copy Dec Closing balances to Jan
3. Clear Rest

Your new Mater is then ready for next year

[Winon]

@ sintek,

Great idea, but you could use .csv as well, and not only pdf.

Regards.

[Sintek]

Ja Nee Winon....Reg so

[dramond1975]

Thanks for all the responses. I just got in to work and will try them. I appreciate the help.