On Mar 5, 12:57 pm, Vergel Adriano
wrote:
There are ways to access and modify the VBA code module of an object in the
workbook. But to make it simple:

1. Instead of creating a new worksheet each time, do a copy instead. Create
a worksheet in your workbook that will have the macro that you need and hide
that sheet.
2. Assuming that sheet is Sheet2, you can do this to make a copy

Dim sht As Worksheet
With ThisWorkbook
Sheet2.Copy After:=.Sheets(.Sheets.Count)
Set sht = .Sheets(.Sheets.Count)
sht.Name = sht.CodeName
sht.Visible = xlSheetVisible
End With
Set sht = Nothing

3. The copy of Sheet2 will then have all the VBA code as well.

On important thing to keep in mind is the code that you put in Sheet2 will
have to be generic. There should be no references to Sheet2, if you need to
refer to the sheet, use "Me" instead.

One other way to do this might be to put your code in the ThisWorkbook
module instead of the individual sheets.

" wrote:
Hi,

I'm creating a workbook that will has an macro on sheet 1 which
creates a new worksheet with a table for tracking jobs.

What I need to do now is add some code to each new worksheet as part
of the macro on sheet 1.

This is where I have no idea what to do. The code on each worksheet is
basically code to run some batch files when a certain cell is
clicked.

What is the best way to do this?

Thanks

Gord & Vergel,

Thanks very much for your suggestions.

I tried both ideas and eventually went for Vergel's idea because I
can't be sure that each machine that this spreadsheet will be used on
will have the template installed.

Thanks again to both of you.