Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding macro code to a new worksheet
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding macro code to a new worksheet
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, 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding macro code to a new worksheet
;7346746 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? ThanksIt's all very well explained and worked out here http://www.cpearson.com/excel/vbe.htm regards, Simon -- Simon Lloyd |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding macro code to a new worksheet
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding macro code to a new worksheet
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding code to a macro | Excel Worksheet Functions | |||
Errors in VB Code since adding macro | Excel Discussion (Misc queries) | |||
Adding VB code to worksheet programatically | Excel Programming | |||
Adding Worksheet_SelectionChange code by macro | Excel Programming | |||
Adding new worksheet from template in code | Excel Programming |