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

One method.

Create a single sheet workbook and save as SHEET.xlt template with the code and
table as you like.

Store it in your XLSTART folder and insert it with this line in your Sheet1
macro code.

Sheets.Add(Type:="Worksheet")

Which will use your SHEET.xlt template as the "added" sheet.


Gord Dibben MS Excel MVP


On 4 Mar 2007 17:37:54 -0800, (E-Mail Removed) 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

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.




"(E-Mail Removed)" 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
>
>

On Mar 5, 12:57 pm, Vergel Adriano
<VergelAdri...@discussions.microsoft.com> 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.
>
> "d...@firstbytecomputers.net" 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.