Here is how you can record a macro in Excel to be used in any workbook from your computer:
This post assumes you know how to record a basic macro.
Normally, when you create a macro in Excel, it is stored only in the workbook in which you are working. However, Excel does offer the option to store it in a separate workbook that you can access with any file on your computer.
When creating a macro, after clicking on the Record macro button in the lower left corner of your workbook:
Name it (in this case “StyleHeader”), and choose a letter to activate it (I picked “Ctrl m”). From the drop down menu, choose “Personal Macro Workbook”:
This will store the macro in a separate workbook called PERSONAL.XLSB. Now you will be able to access the macro from any workbook on your computer:
The personal workbook is opened as a hidden workbook every time you start Excel. Macros saved in the Personal Workbook can not be accessed if the PERSONAL.XLSB file is closed.