Is there a file saver Macro generator?

[excelos]

Hello

I have created a sheet with lots of formulas and text after a lot of effort and time.

I want to make a Macro that will just create that sheet and type everything that I have typed.

The suggested method to go through every cell and click F2 and then Enter, while recording a Macro, is not applicable because I have hundreds of cells filled.

Any idea?

 

[Steve41553]

HelloI have created a sheet with lots of formulas and text after a lot of effort and time.I want to make a Macro that will just create that sheet and type everything that I have typed.The suggested method to go through every cell and click F2 and then Enter, while recording a Macro, is not applicable because I have hundreds of cells filled.Any idea?

Do you need to create the sheet every time? If you saved it as a template you could load it and it would be ready for use.

 

[Yongle]

If you do this manually, does it do what you want?
right-click on sheet tab \ Move or Copy \ Create a Copy \ OK

If so:

Sub CopyActiveSheet()
    ActiveSheet.Copy Before:=ActiveSheet
End Sub

 

[excelos]

If you do this manually, does it do what you want?
right-click on sheet tab \ Move or Copy \ Create a Copy \ OK

If so:

Sub CopyActiveSheet()
    ActiveSheet.Copy Before:=ActiveSheet
End Sub

I don't want to copy the sheet, I want to generate it!

 

[excelos]

Do you need to create the sheet every time? If you saved it as a template you could load it and it would be ready for use.

That may be a solution, how do I do that?

 

[Steve41553]

That may be a solution, how do I do that?

This is part of the code for one of my programs. Further lines copy downloaded data from a second file, redefine named ranges depending on the number of lines, and update (not create) pivot tables. Presumably similar to what you've just done, but if I want to change the layout I edit the template rather than the code. Workbooks.Open Filename:= "DETAILED-TRIAL-BALANCE-TEMPLATE.xlsx"

 

[excelos]

This is part of the code for one of my programs. Further lines copy downloaded data from a second file, redefine named ranges depending on the number of lines, and update (not create) pivot tables. Presumably similar to what you've just done, but if I want to change the layout I edit the template rather than the code. Workbooks.Open Filename:= "DETAILED-TRIAL-BALANCE-TEMPLATE.xlsx"

Thanks but how/where do I save the template?
Will the template load the references to a specifically named sheet?

 

[excelos]

Thanks but how/where do I save the template?
Will the template load the references to a specifically named sheet?

I need to load the template as sheet in the current workbook, because it has references to the current's workbook sheet.

 

[Steve41553]

Apologies for the delay. I have to reply from home as the board is often blocked at work.

Hopefully our approaches are not that different. I save the template in a specific folder and the code is in a different file. It works on the template creates a file name which is derived from the month. It is the file name that changes, not the tab names.