Back to Excel Newsletter Archives

OzGrid Excel and VBA Newsletter March 2006

Search Tips

Newsletter Index

EXCEL TIPS AND TRICKS | EXCEL VBA TIPS AND TRICKS

EXCEL TIPS AND TRICKS

Current Special! Complete Excel Excel Training Course for Excel 97 - Excel 2003, only $145.00. $59.95 Instant Buy/Download

A template is like a dress pattern -  it is a model that is used over and over again containing specified styles.  If you have a need in Excel to constantly change the default settings of a workbook or worksheet, then you can use a template to save your personalized specific settings and tell Excel to load your settings at your command or as a default. 
 
Normally, when you Start Excel, a new workbook is automatically presented to you.  This is the book.xlt template (xlt is the file extension for a template).   The default book.xlt template contains the standard settings for a new Excel workbook, such as an Arial, 20pt black font, grey gridlines showing, white background etc.  Excel uses its default Book.xlt template when Starting Excel, or from within Excel when you create a new workbook without specifying a template.
 
You can create templates in Excel for both worksheets and workbooks and save these templates to either your Start folder , or assign them to the Templates dialog under their own tab for selection .  A template assigned to the Start folder will automatically load when Excel is opened and Excel will use this template to create a blank workbook when you Start Excel, or create a new workbook without specifying a template.  Any sheets inserted into the workbook will also use the template.
 
WORKBOOK TEMPLATES
 
Workbook templates can contain things such as specific formatting styles, column/row labels, headers and footers, formulas and even macros.  Workbook templates are used for settings you want in all new workbooks based on the template. 
 
To create a workbook template:
 
Create a new Excel workbook containing all your specified settings.
Go to File>Save As
In the Save as type box, select Template
In the Save in box, select the folder you wish to store your template in.
 
Note: If you wish the first page of your template to be viewed as a picture in the Preview box of the Templates dialog, go to File>Properties, click the Summary tab and select Save preview picture before saving.
 
WORKSHEET TEMPLATES
 
Worksheet templates are used to store settings you want to appear on all new sheets of the same type.  The default worksheet template is stored as sheet.xlt.  When you create a template for a worksheet, Excel uses the template to create blank worksheets when you insert a new sheet to a workbook.
 
To create a worksheet template:
 
Create a new Excel workbook containing one worksheet
One the worksheet specify all required settings
Go to File>Save As
In the Save as type box, select Template
In the Save in box, select the folder you wish to store your template in
 
SAVING TO THE XLStart FOLDER
 
If you want your template to be available as soon as you Start Excel, you need to save directly to the XLStart folder, usually located at:
 
C:\Program Files\Microsoft Office\Office11\XLStart
 
SAVING TO THE TEMPLATES FOLDER
 
If you wish your template to be available in the Templates dialog, try the following:
 
Create a new Excel workbook or worksheet
Specify all required settings
Go to File>Save As
Locate the Templates Folder, usually C:\Documents and Settings\user_name\Application Data\Microsoft\Templates
In the Save as type box, select Template
Give your template a name.
 
This will force your template to appear in the Templates dialog under the General tab. 
 
Note: Sometimes, due to network restrictions, the Templates folder cannot be seen.  If this happens to you, you need to contact your network administrator.
 
You can go one step further and create a new folder within the Templates folder C:\Documents and Settings\user_name|Application Data\Microsoft\Templates and move your template to that folder.  This will create a new tab on the templates dialog.
 

 
 
To remove a template, you need to locate the folder that it is saved in and delete it.
 
USEFUL TEMPLATES

See our huge range of Excel Templates

See also Microsoft's huge range of free excel templates

Current Special! Complete Excel Excel Training Course for Excel 97 - Excel 2003, only $145.00. $59.95 Instant Buy/Download,30 Day Money Back Guarantee & Free Excel Help for LIFE!

Got any Excel Questions? Free Excel Help .

CREATING WORKSHEETS FOR EACH ITEM IN A TABLE

I'm often asked how one could create x number of Worksheets where each one houses the data specific to each item in a table. Let's say we have data set out like shown below.

The screen shot is simply that of properly laid out table of data (A1:E12) where the first column is headed "Description". Underneath the "Description" header there are items, some of which exist more than once.

The task at hand is to create 1 Worksheet for each item (named as the item) and have all associated data on it.

You can download a working example of the code below here

Sub PagesByDescription()Dim rRange As Range, rCell As RangeDim wSheet As WorksheetDim wSheetStart As WorksheetDim strText As String    Set wSheetStart = ActiveSheet    wSheetStart .AutoFilterMode = False    'Set a range variable to the correct item column    Set rRange = Range("A1", Range("A65536").End(xlUp))            'Delete any sheet called "UniqueList"        'Turn off run time errors & delete alert        On Error Resume Next        Application.DisplayAlerts = False        Worksheets("UniqueList").Delete                'Add a sheet called "UniqueList"        Worksheets.Add().Name = "UniqueList"                   'Filter the Set range so only a unique list is created            With Worksheets("UniqueList")                rRange.AdvancedFilter xlFilterCopy, , _                 Worksheets("UniqueList").Range("A1"), True                                  'Set a range variable to the unique list, less the heading.                 Set rRange = .Range("A2", .Range("A65536").End(xlUp))            End With                        On Error Resume Next            With wSheetStart For Each rCell In rRange                  strText = rCell                 .Range("A1").AutoFilter 1, strText                    Worksheets(strText).Delete                    'Add a sheet named as content of rCell                    Worksheets.Add().Name = strText                    'Copy the visible filtered range _                    (default of Copy Method) and leave hidden rows                    .UsedRange.Copy Destination:=ActiveSheet.Range("A1")                    ActiveSheet.Cells.Columns.AutoFit                Next rCell            End With                    With wSheetStart .AutoFilterMode = False            .Activate        End With                On Error GoTo 0        Application.DisplayAlerts = TrueEnd Sub

Software Categories Search Software