Macro to insert a template?

[excelos]

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!

 

[Kamolga]

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

=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]

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

=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.

 

[Kamolga]

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

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

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

Private Sub Workbook_Open()


End Sub

which needs to be put into thisworkbook object