In Excel, how do you record a macro that can be universally used by any Excel document?

Question

I see how to record a macro, and I've been able to use this feature, but the macro apprears to be local only to the Excel document in which I record the macro. Is it possible to record a universal excel macro that will work for ANY document that is opened with Excel?

Answer 1

I generally have a macro .xls sheet and sheets with data in them. That way I can change the macro code and upgrade without having to re-enter data.

I like to put macros under buttons. To assign a macro from one sheet to a button in another, open both sheets and put the one you want the button in on top and the one with the macro somewhere else, but open. You can click on buttons in the Forms Toolbar and then drag, draw a button on your spreadsheet.

When it is drawn, it prompts you for the macro to assign. In the list is shows the macros from all open workbooks, or it should. If it doesn't look at "Macros in:" near the bottom and change it if needed. Select the macro in the other sheet and there you go. If you open only the sheet with the button and click it, Excel will open the macro sheet for you.

You can place macro files in special folders so they are opened every time you start excel, but you don't have to.

Answer 2

After pressing Record - choose Store macro in: Personal Macro Workbook. That would do the trick.

Answer 3

Yes, but you will have to save it as an xls file format for an earlier version. When you choose the file type, you will see the option to save it as an Excel 5.0 or a 4.0 document.

Being that most people use later versions that will allow the macro to work.

Answer 4

The best thing to do is to create your own template. If you create your macro and then save as TEMPLATE and name it GLOBAL or something.

To use it, you'd say New / Template on my computer / and you will see it there in the Main category. I'd say that's easiest way.

You could copy a macro to another workbook - but to make it global, I would create a template and you could even store it on a shared drive so everyone could have the use of it. There's probably other ways - but that's the easy one.