When setting up a macro, if I want to access it on any Excel worksheet how do I save it?

Dear Nicola

Thank you for attending Excel Advanced Course. I hope you enjoyed the course and it was both enjoyable as well as informative!!

There are two possible solutions to the query you have asked. The first one is as follows:

Step 1: Choose Tools > Macro > Record New Macro…

Step 2: In the Record new Macro Dialog box choose Personal Macro Workbook from the Store macro in section.

This will ensure that the macro will be available to any opened file/s. Excel creates a hidden personal workbook and saves the macro in this workbook. Therefore whenever you start MS Excel the hidden workbook called Personal is automatically opened and the Macro is available to any opened Excel file.

If you have chose the option of this workbook only in the Record Macro dialog box and now wish to copy the macro to an existing workbook then please follow these steps:

Step 1: Open both the files that contain the macro as well as the file that you wish to copy the macro into.

Step 2: Select the file that contains the Macro.

Step 3: Choose Tools > Macro > Macros….

Step 4: Choose the macro that you wish to copy and then click Edit button

Step 5: You will see the Visual Basic Editor window and on the left hand side you should be able to see all the opened files as VBAProject(FileName)

Step 6: You should be able to see a folder called Module. Drag and drop the relevant module to the relevant file you wish to copy the macro into.

The macro will be available in the file you copied the macro into.

I hope this has answered your query.

If this has answered your query then I would request you to please mark the question as resolved!! If not and you have a specific question related to this then please post it as a new question and we should be able to provide you the solution for it!!


Kindest Regards


Rajeev Rawat
MOS Master Instructor 2000 and 2003
MCAS Master Instructor 2007

Thanks Raj.

Also I run alot of reports from a different application that we use and import the information into Excel. I then format and delete rows and columns that I dont need to make the information more presentable.
On one report that I run I was thinking about setting up a Macro to help me format things quicker.
I delete rows where it has totaled sections, but I want it as one document, however the amount of rows between each section differs from week to week. Can I still use the Macro becuase it will delete that information, or will it always delete the specific row??