Discussion Locked

XL: a macro available to all w'sts

0 helpful
5 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I saved a macro to only that worksheet.
How do I make it available to all worksheets.
Currently I have to open the original one, which is a pain. I have other macros that are available in all new worksheets.
Thanks

5 replies

by graemeh

Trevor,
You need to copy the macro into a code module in your Personal.xls workbook. If you have macros that work in all workbooks then Personal.xls probably already exists (look in the list of open workbooks in the VBA IDE). If you can't see it then you can create it by recording a macro and choosing to save it in 'Personal Macro Workbook'.

by jeff adams

This is right out of Excel help:

====================
Create a workbook template for new workbooks

1 Create a workbook that contains the sheets, default text (such as page headers and column and row labels), formulas, macros, styles, and other formatting you want in new workbooks that you base on the template.
2 On the File menu, click Save As.
3 In the Save as type box, click Template (*.xlt).
4 In the Save in box, select the folder where you want to store the template.
To create the **DEFAULT WORKBOOK TEMPLATE**, select either the XLStart folder in the Microsoft Excel folder or the alternate startup folder.
To create a custom workbook template, select the Templates folder in your Office folder or Microsoft Excel folder.
5 In the File name box, type book to create a default workbook template.
To create a custom template, type any valid file name.
====================

====================
default workbook template

The Book.xlt template you create when you wantto change the default format of new blank workbooks. Microsoft Excel uses the template to create the blank workbook that opens when you start Microsoft Excel, click the New button on the Standard toolbar, or select the Workbook template in the New dialog box (File menu). Unless you create the Book.xlt template, Microsoft Excel bases the new blank workbook on built-in program settings. You must store the Book.xlt template in the Xlstart folder or in the alternate startup folder. The Xlstart folder is in your Microsoft Excel folder or, if you are using Microsoft Office, in the Office folder.
====================

So, if you follow the above instructions and create the blank.xlt, put your macro in it, and save it in your XLStart directory (e.g. C:\Program Files\Microsoft Office\Office\XLStart), it should be available for all new workbooks. (This is the equivalent of Word's normal.dot.)

-Jeff

This discussion is closed to new replies.