Add Sheet - Copy Worksheet

[Guest]

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

 

[SmilingPolitely]

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.

 

[Guest]

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

 

[William]

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

(e-mail address removed)

 

[Guest]

This worked great ... thanks for your help!

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

(e-mail address removed)

 

[Guest]

Thanks for your help!

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.

 

[Guest]

Got it! Thanks for your help!

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