Programming a new macro

[Guest]

I have a macro that automatically controls the format of cells in a worksheet.
Example:
Sheet1
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then Range("A1").EntireColumn.AutoFit
End Sub


Now I want make a macro that insert a new worksheet and the loads this
automatic macro into the new worksheet. Is this possible?

BR
Trond

 

[Nigel]

Save the workbook with the code as a template and then when you require a
new workbook open the template, this will create a new workbook (with the
code) and rename the workbook sequentially e.g. if you called the template
MyCodeBook then when you open the template the new workbook will be named
MyCodeBook1, the next one MyCodeBook2 etc....

 

[Guest]

Sorry, I made myself unclear.

I have an existing workbook with several worksheets. These sheets have
automatically macros that formats the content by using Worksheet_Change. I
want to insert another sheet in my workbook. I want this sheet to contain
this automatic macro that formats the content in my new sheet. Do I have to
copy the text into the sheet in VBA or can I make a macro that does this job
for me?

Trond

Nigel skrev:

 

[Guest]

Hi Trond,

Try creating a "Master" wksheet in the wkbook which is a hidden wksheet. In
the "Master" Wksheet place the Worksheet_Change code and use the following
code on a button in the wkbook each time you add a new sheet to the wkbook.

ActiveWorkbook.Sheets("Master").Copy after:=Worksheets("Report")
ActiveSheet.Visible = True

This is a snipet of the code that I use in my particular workbook. Place
the new sheet anywhere you want

Hope this is of assistance
Rgds/sgl

 

[Guest]

Thank you! Briliant!

Trond

sgl skrev:

Hi Trond,

Try creating a "Master" wksheet in the wkbook which is a hidden wksheet. In
the "Master" Wksheet place the Worksheet_Change code and use the following
code on a button in the wkbook each time you add a new sheet to the wkbook.

ActiveWorkbook.Sheets("Master").Copy after:=Worksheets("Report")
ActiveSheet.Visible = True

This is a snipet of the code that I use in my particular workbook. Place
the new sheet anywhere you want

Hope this is of assistance
Rgds/sgl