Macro to insert a template?

excelos

Active Member
Joined
Sep 25, 2011
Messages
329
Hello

I have a template that is full of formulas, text and formatting.

When I open any new excel file, I want to click a button and load that template in a new sheet so that the formulas will be populated based on the file I opened.

How do I do this without converting all the text and formatting of the template into VBA code which will be a nightmare?

I want just the template sheet to show up and then do some search-and-replace to fix the references and that should be it!

Thanks!
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Kamolga

Well-known Member
Joined
Jan 28, 2015
Messages
1,176
Hi,

It is quite abstract without seeing the formulae and template but an easy way is to move the template in your file and have named ranges (formula tab, define name) in your formulae.
Let's say in sheet1 A3 you have the amount of worked days and in sheet 1 a4 you have a rate and your formula in template must multiply a3 by a4.

Clicking on a3 then name it WorkedDays and then name a4 rate. If your template has a formula
Code:
=WorkedDays*rate
you can use it with every file that have those named ranges.
Note that if workeDdays changes cell in every file, you can have a macro that assign their value to the namedrange as well or if you need a human to do it, you can have a select range pop up that asks "Please select worked day", then "please select rate", etc.
 

excelos

Active Member
Joined
Sep 25, 2011
Messages
329
Hi,

It is quite abstract without seeing the formulae and template but an easy way is to move the template in your file and have named ranges (formula tab, define name) in your formulae.
Let's say in sheet1 A3 you have the amount of worked days and in sheet 1 a4 you have a rate and your formula in template must multiply a3 by a4.

Clicking on a3 then name it WorkedDays and then name a4 rate. If your template has a formula
Code:
=WorkedDays*rate
you can use it with every file that have those named ranges.
Note that if workeDdays changes cell in every file, you can have a macro that assign their value to the namedrange as well or if you need a human to do it, you can have a select range pop up that asks "Please select worked day", then "please select rate", etc.

I think you have not understood at all what I am saying and what I am trying to do, can you please re-read what I wrote?
You do not need to see the formulas or the template. You can imagine tons of formulas and tons of formatting on the template and that should be enough.
I will try to explain again: I have a complicated template that I want to reintroduce as a new sheet to every excel file I open. How can I do that with a macro? It is impossible to write a macro that will generate the template from scratch because there are tons of formulas and formatting and the resulting VBA code will be tons of lines.
 
Last edited:

Kamolga

Well-known Member
Joined
Jan 28, 2015
Messages
1,176
Then don't mention the formulae and simply ask how to move a sheet from a template to a workbook, giving path and name if you want a detailed answer.
Lets consider you have Book1 which has one sheet and you want to move your template sheet after it.

Once your template is opened (you can open it with .open and path), in this case the sheet I want to move from that file is called templateSheet.

Then
Code:
Windows("Array_Template.xlsm").Activate
    Sheets("TemplateSheet").Select
    Sheets("TemplateSheet").Move After:=Workbooks("Book1").Sheets(1)

Of course you can dimension WB1 as Template, wb2 as activeworkbook and simply have

Code:
wb1.[LEFT][COLOR=#222222][FONT=Verdana]Sheets("TemplateSheet").Move After:=wb2.Sheets(1)[/FONT][/COLOR][/LEFT]

If you want the macro to be launched on opening file, you need an open workbook event
Code:
Private Sub Workbook_Open()


End Sub

which needs to be put into thisworkbook object
 

Watch MrExcel Video

Forum statistics

Threads
1,109,522
Messages
5,529,330
Members
409,863
Latest member
stacy09
Top