You create a macro in Excel by running the macro recorder or by using the Visual Basic for Applications (VBA) editor. After you've created your macro, you'll want to save it so you can use it again in the current workbook or in other workbooks.
Save a macro with the current workbook
If you'll need the macro only in the workbook where you created it, click Save or Save As like you normally would. But saving a workbook with macros is a little different because it needs to be in a special "macro-enabled" file format. So when you try to save it, Excel prompts you with two choices:
Save it as a macro-enabled workbook (*.xlsm file type) by clicking No.
Save it as a macro-free workbook by clicking Yes.
To save it as a macro-enabled workbook:
In the Save As box, in the Save as type list box, choose Excel Macro-Enabled Workbook (*.xlsm).
Create and save the macro in your Personal workbook
To make your macros available every time you open Excel, create them in a workbook called Personal.xlsb. That’s a hidden workbook stored on your computer, which opens every time you start Excel. See Create and save all your macros in a single workbook to learn how to do this.