|
|
|
|
|
|
|
|
|
Excel
|
Add Numbered Sheets From Template
|
|
|
Ease of Use
|
Easy
|
|
Version tested with
|
2003
|
|
Submitted by:
|
lucas
|
|
Description:
|
Add additional numbered sheets(Page 2, Page 3, etc.) from a template that does not have the instructions that are on the first sheet(in the example the instructions are a picture of roof joists from a building construction bill of materials). The idea being not to have the picture on all sheets, only the first one.
|
|
Discussion:
|
In the example file I have used a joist bridging worksheet which requires the user to input many dimensions. Where to get the dimensions is detailed in a picture that is included on the first sheet. There are often not enough rows on the first sheet, in fact it often takes 6-12 additional sheets. I do not wish to include the detailed picture on the additional sheets so I use a hidden sheet as a template. When "add sheet" is clicked a new numbered worksheet is added without the detail.
|
|
Code:
|
instructions for use
|
Place this code In a standard module:
Option Explicit
Sub AddNewSheets()
Dim TotalSheets As Variant
TotalSheets = Worksheets.Count - 1
Worksheets("Blank Sheet").Activate
ActiveSheet.Copy after:=Worksheets("Page " & TotalSheets)
Worksheets("Blank Sheet (2)").Activate
ActiveSheet.Name = "Page " & TotalSheets + 1
ActiveSheet.Visible = True
End Sub
|
|
How to use:
|
- Create your first page (which must be named "Page 1" unless you change it in the code. Whatever you use will have an incremented number added to it)
- Next create a template from it without the detail and it must be named "Blank Sheet"(this can be changed if you change it in the code). This sheet can be hidden. Be sure to leave the button on the template so you can keep adding sheets as necessary.
|
|
Test the code:
|
- Run the macro AddNewSheets
- To unhide the template go to format-sheet-unhide
|
|
Sample File:
|
add sheets from template.zip 75.8KB
|
|
Approved by mdmackillop
|
|
This entry has been viewed 187 times.
|
|
|