Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add Sheet - Copy Worksheet
I'd like to add four buttons to a worksheet. When any of the buttons are
clicked, a sheet will be added (to the end) of a workbook. Then, depending on which button is clicked, one of four worksheet templates will be copied onto the new sheet. The four templates could either be four separate files, or they could be separate sheets in one file ... whichever works better/easier.... Help, please! Cindy |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add Sheet - Copy Worksheet
ckrogers wrote:
I'd like to add four buttons to a worksheet. When any of the buttons are clicked, a sheet will be added (to the end) of a workbook. Then, depending on which button is clicked, one of four worksheet templates will be copied onto the new sheet. The four templates could either be four separate files, or they could be separate sheets in one file ... whichever works better/easier.... Help, please! Cindy From Excel 2003 Help.... Insert a new sheet that's based on a custom template You must have already created a custom sheet template. How? Decide which type of template you want: Workbook template Create a workbook that contains the sheets, default text (such as page headers and column and row labels), formulas, macros (macro: An action or a set of actions you can use to automate tasks. Macros are recorded in the Visual Basic for Applications programming language.), styles (style: A combination of formatting characteristics, such as font, font size, and indentation, that you name and store as a set. When you apply a style, all of the formatting instructions in that style are applied at one time.), and other formatting you want in new workbooks based on the template. Worksheet template Create a workbook that contains one worksheet. On the worksheet, include the formatting, styles (style: A combination of formatting characteristics, such as font, font size, and indentation, that you name and store as a set. When you apply a style, all of the formatting instructions in that style are applied at one time.), text, and other information you want to appear on all new sheets of the same type. To display a picture of the first page of a template in the Preview box of the Templates dialog box (General Templates..., New Workbook task pane), click Properties on the File menu, click the Summary tab, and then select the Save preview picture check box. On the File menu, click Save As. In the Save as type box, click Template. In the Save in box, select the folder where you want to store the template. To create the default workbook template (default workbook template: The Book.xlt template that you create to change the default format of new workbooks. Excel uses the template to create a blank workbook when you start Excel or create a new workbook without specifying a template.) or default worksheet template (default worksheet template: The Sheet.xlt template that you create to change the default format of new worksheets. Excel uses the template to create a blank worksheet when you add a new worksheet to a workbook.), select either the XLStart folder or the alternate startup folder (alternate startup folder: A folder in addition to the XLStart folder that contains workbooks or other files that you want to be opened automatically when you start Excel and templates that you want to be available when you create new workbooks.). The XLStart folder is usually C:\Program Files\Microsoft Office\Office11\XLStart To create a custom workbook or worksheet template, select the Templates folder, which is usually C:\Documents and Settings\user_name\Application Data\Microsoft\Templates Then just Sheets.Add Type:="your user path\templateName.xlt" Hope this helps. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add Sheet - Copy Worksheet
try:-
Option Explicit Public Sub AddSheet() ' add a blank sheet AddTemplateSheet "" 'copy from a template AddTemplateSheet "Template1" End Sub Private Sub AddTemplateSheet(template As String) Dim ws As Worksheet Dim source As Range Set ws = Worksheets.Add(after:=Worksheets(Worksheets.Count) ) If template < "" Then Set source = Worksheets(template).UsedRange With ws.Range(source.Address) .Formula = source.Formula End With End If ws.Activate End Sub "ckrogers" wrote: I'd like to add four buttons to a worksheet. When any of the buttons are clicked, a sheet will be added (to the end) of a workbook. Then, depending on which button is clicked, one of four worksheet templates will be copied onto the new sheet. The four templates could either be four separate files, or they could be separate sheets in one file ... whichever works better/easier.... Help, please! Cindy |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add Sheet - Copy Worksheet
Hi
Firstly, create your 4 templates - they should consist of one sheet each. Save them to the same location where the current workbook that will execute the code is located. Name the templates "wb1.xlt", "wb2.xlt", "wb3,xlt" and" wb4.xlt". Secondly, copy the code below into a general module of your workbook. To create the buttons on a worksheet, run the macro "ButtonsOnWorksheet" - I have assumed the sheet on which you want the buttons places is called "MySheet" - change the worksheet name in the macro code as appropriate. Sub ButtonsOnWorksheet() Dim b As Button, t As Long, h As Long Dim l As Long, w As Long With ThisWorkbook.Sheets("MySheet") t = 0 h = 0 l = 95 w = 21 Set b = .Buttons.Add(t, h, l, w) ..DrawingObjects.Delete Set b = .Buttons.Add(t, h, l, w) b.OnAction = ThisWorkbook.Name & "!getbook1" b.Characters.Text = "Book 1" t = t + l + 10 Set b = .Buttons.Add(t, h, l, w) b.OnAction = ThisWorkbook.Name & "!getbook2" b.Characters.Text = "Book 2" t = t + l + 10 Set b = .Buttons.Add(t, h, l, w) b.OnAction = ThisWorkbook.Name & "!getbook3" b.Characters.Text = "Book 3" t = t + l + 10 Set b = .Buttons.Add(t, h, l, w) b.OnAction = ThisWorkbook.Name & "!getbook4" b.Characters.Text = "Book 4" End With End Sub Sub getbook1() Dim wb As Workbook, ws As Worksheet, i As Integer Set wb = ThisWorkbook wb.Sheets.Add Type:=ThisWorkbook.Path & "\wb1.xlt" i = wb.Sheets.Count Set ws = ActiveSheet ws.Move After:=wb.Sheets(i) End Sub Sub getbook2() Dim wb As Workbook, ws As Worksheet, i As Integer Set wb = ThisWorkbook wb.Sheets.Add Type:=ThisWorkbook.Path & "\wb2.xlt" i = wb.Sheets.Count Set ws = ActiveSheet ws.Move After:=wb.Sheets(i) End Sub Sub getbook3() Dim wb As Workbook, ws As Worksheet, i As Integer Set wb = ThisWorkbook wb.Sheets.Add Type:=ThisWorkbook.Path & "\wb3.xlt" i = wb.Sheets.Count Set ws = ActiveSheet ws.Move After:=wb.Sheets(i) End Sub Sub getbook4() Dim wb As Workbook, ws As Worksheet, i As Integer Set wb = ThisWorkbook wb.Sheets.Add Type:=ThisWorkbook.Path & "\wb4.xlt" i = wb.Sheets.Count Set ws = ActiveSheet ws.Move After:=wb.Sheets(i) End Sub -- ----- XL2002 Regards William "ckrogers" wrote in message ... I'd like to add four buttons to a worksheet. When any of the buttons are clicked, a sheet will be added (to the end) of a workbook. Then, depending on which button is clicked, one of four worksheet templates will be copied onto the new sheet. The four templates could either be four separate files, or they could be separate sheets in one file ... whichever works better/easier.... Help, please! Cindy |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add Sheet - Copy Worksheet
This worked great ... thanks for your help!
"William" wrote: Hi Firstly, create your 4 templates - they should consist of one sheet each. Save them to the same location where the current workbook that will execute the code is located. Name the templates "wb1.xlt", "wb2.xlt", "wb3,xlt" and" wb4.xlt". Secondly, copy the code below into a general module of your workbook. To create the buttons on a worksheet, run the macro "ButtonsOnWorksheet" - I have assumed the sheet on which you want the buttons places is called "MySheet" - change the worksheet name in the macro code as appropriate. Sub ButtonsOnWorksheet() Dim b As Button, t As Long, h As Long Dim l As Long, w As Long With ThisWorkbook.Sheets("MySheet") t = 0 h = 0 l = 95 w = 21 Set b = .Buttons.Add(t, h, l, w) ..DrawingObjects.Delete Set b = .Buttons.Add(t, h, l, w) b.OnAction = ThisWorkbook.Name & "!getbook1" b.Characters.Text = "Book 1" t = t + l + 10 Set b = .Buttons.Add(t, h, l, w) b.OnAction = ThisWorkbook.Name & "!getbook2" b.Characters.Text = "Book 2" t = t + l + 10 Set b = .Buttons.Add(t, h, l, w) b.OnAction = ThisWorkbook.Name & "!getbook3" b.Characters.Text = "Book 3" t = t + l + 10 Set b = .Buttons.Add(t, h, l, w) b.OnAction = ThisWorkbook.Name & "!getbook4" b.Characters.Text = "Book 4" End With End Sub Sub getbook1() Dim wb As Workbook, ws As Worksheet, i As Integer Set wb = ThisWorkbook wb.Sheets.Add Type:=ThisWorkbook.Path & "\wb1.xlt" i = wb.Sheets.Count Set ws = ActiveSheet ws.Move After:=wb.Sheets(i) End Sub Sub getbook2() Dim wb As Workbook, ws As Worksheet, i As Integer Set wb = ThisWorkbook wb.Sheets.Add Type:=ThisWorkbook.Path & "\wb2.xlt" i = wb.Sheets.Count Set ws = ActiveSheet ws.Move After:=wb.Sheets(i) End Sub Sub getbook3() Dim wb As Workbook, ws As Worksheet, i As Integer Set wb = ThisWorkbook wb.Sheets.Add Type:=ThisWorkbook.Path & "\wb3.xlt" i = wb.Sheets.Count Set ws = ActiveSheet ws.Move After:=wb.Sheets(i) End Sub Sub getbook4() Dim wb As Workbook, ws As Worksheet, i As Integer Set wb = ThisWorkbook wb.Sheets.Add Type:=ThisWorkbook.Path & "\wb4.xlt" i = wb.Sheets.Count Set ws = ActiveSheet ws.Move After:=wb.Sheets(i) End Sub -- ----- XL2002 Regards William "ckrogers" wrote in message ... I'd like to add four buttons to a worksheet. When any of the buttons are clicked, a sheet will be added (to the end) of a workbook. Then, depending on which button is clicked, one of four worksheet templates will be copied onto the new sheet. The four templates could either be four separate files, or they could be separate sheets in one file ... whichever works better/easier.... Help, please! Cindy |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add Sheet - Copy Worksheet
Thanks for your help!
"SmilingPolitely" wrote: ckrogers wrote: I'd like to add four buttons to a worksheet. When any of the buttons are clicked, a sheet will be added (to the end) of a workbook. Then, depending on which button is clicked, one of four worksheet templates will be copied onto the new sheet. The four templates could either be four separate files, or they could be separate sheets in one file ... whichever works better/easier.... Help, please! Cindy From Excel 2003 Help.... Insert a new sheet that's based on a custom template You must have already created a custom sheet template. How? Decide which type of template you want: Workbook template Create a workbook that contains the sheets, default text (such as page headers and column and row labels), formulas, macros (macro: An action or a set of actions you can use to automate tasks. Macros are recorded in the Visual Basic for Applications programming language.), styles (style: A combination of formatting characteristics, such as font, font size, and indentation, that you name and store as a set. When you apply a style, all of the formatting instructions in that style are applied at one time.), and other formatting you want in new workbooks based on the template. Worksheet template Create a workbook that contains one worksheet. On the worksheet, include the formatting, styles (style: A combination of formatting characteristics, such as font, font size, and indentation, that you name and store as a set. When you apply a style, all of the formatting instructions in that style are applied at one time.), text, and other information you want to appear on all new sheets of the same type. To display a picture of the first page of a template in the Preview box of the Templates dialog box (General Templates..., New Workbook task pane), click Properties on the File menu, click the Summary tab, and then select the Save preview picture check box. On the File menu, click Save As. In the Save as type box, click Template. In the Save in box, select the folder where you want to store the template. To create the default workbook template (default workbook template: The Book.xlt template that you create to change the default format of new workbooks. Excel uses the template to create a blank workbook when you start Excel or create a new workbook without specifying a template.) or default worksheet template (default worksheet template: The Sheet.xlt template that you create to change the default format of new worksheets. Excel uses the template to create a blank worksheet when you add a new worksheet to a workbook.), select either the XLStart folder or the alternate startup folder (alternate startup folder: A folder in addition to the XLStart folder that contains workbooks or other files that you want to be opened automatically when you start Excel and templates that you want to be available when you create new workbooks.). The XLStart folder is usually C:\Program Files\Microsoft Office\Office11\XLStart To create a custom workbook or worksheet template, select the Templates folder, which is usually C:\Documents and Settings\user_name\Application Data\Microsoft\Templates Then just Sheets.Add Type:="your user path\templateName.xlt" Hope this helps. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add Sheet - Copy Worksheet
Got it! Thanks for your help!
"Patrick Molloy" wrote: try:- Option Explicit Public Sub AddSheet() ' add a blank sheet AddTemplateSheet "" 'copy from a template AddTemplateSheet "Template1" End Sub Private Sub AddTemplateSheet(template As String) Dim ws As Worksheet Dim source As Range Set ws = Worksheets.Add(after:=Worksheets(Worksheets.Count) ) If template < "" Then Set source = Worksheets(template).UsedRange With ws.Range(source.Address) .Formula = source.Formula End With End If ws.Activate End Sub "ckrogers" wrote: I'd like to add four buttons to a worksheet. When any of the buttons are clicked, a sheet will be added (to the end) of a workbook. Then, depending on which button is clicked, one of four worksheet templates will be copied onto the new sheet. The four templates could either be four separate files, or they could be separate sheets in one file ... whichever works better/easier.... Help, please! Cindy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy worksheet and rename sheet | Excel Discussion (Misc queries) | |||
How do I copy a graph to new worksheet and get data from new sheet? | Charts and Charting in Excel | |||
Can I copy Everything in a worksheet to a new sheet EXCEPT the te | Excel Worksheet Functions | |||
copy cell from one sheet of worksheet to another sheet | Excel Discussion (Misc queries) | |||
copy data from one worksheet to identical sheet in different workbook | Excel Discussion (Misc queries) |