Is there a file saver Macro generator?

excelos

Active Member
Joined
Sep 25, 2011
Messages
329
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?
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Steve41553

New Member
Joined
Apr 8, 2014
Messages
27
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

Well-known Member
Joined
Mar 11, 2015
Messages
6,746
Office Version
  1. 365
Platform
  1. Windows
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:
Code:
Sub CopyActiveSheet()
    ActiveSheet.Copy Before:=ActiveSheet
End Sub
 

excelos

Active Member
Joined
Sep 25, 2011
Messages
329
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:
Code:
Sub CopyActiveSheet()
    ActiveSheet.Copy Before:=ActiveSheet
End Sub

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

excelos

Active Member
Joined
Sep 25, 2011
Messages
329

ADVERTISEMENT

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

New Member
Joined
Apr 8, 2014
Messages
27
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

Active Member
Joined
Sep 25, 2011
Messages
329

ADVERTISEMENT

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

Active Member
Joined
Sep 25, 2011
Messages
329
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

New Member
Joined
Apr 8, 2014
Messages
27
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,026
Messages
5,526,325
Members
409,696
Latest member
EERS

This Week's Hot Topics

Top