Can macros be saved in any Excel file?

Excel macros allow you to automate all sorts of tasks, from entering commonly used data to automatic printing and saving of files. Once you start to use Excel, it takes no time at all to create a large collection of macros for tasks you perform often. If you purchase a new computer, or if you just want to use Excel on another laptop or PC, you must save your macros in a special macro-enabled format. Once you save the macros this way, you can then move them to another computer or even email them to others so they can use them in their spreadsheets as well.

Saving and Transferring Macros in a Workbook File

  1. 1.

    Start Microsoft Excel on your computer. Create a new spreadsheet with macros as you normally would.

  2. 2.

    Click “File” on the Excel ribbon bar, and then click “Save As.” Enter a name for the workbook in the “File Name” field, and then select “Excel Macro-Enabled Workbook” in the “Save as Type” drop-down list. Click the “Save” button to save the workbook and the macros.

  3. 3.

    Connect a USB flash drive or hard drive to an empty port on your computer. Wait for Windows to detect the drive and configure it for use.

  4. 4.

    Click “Start,” then “Computer.” Browse to the folder where you saved the Excel workbook with the saved macros.

  5. 5.

    Highlight the name of the Excel macro-enabled workbook and then press the “Ctrl-C” keys to copy it to the Windows Clipboard.

  6. 6.

    Return to the main Windows Explorer window. Double-click the drive letter of the USB drive to open it and display its contents. Press “Ctrl-V” to copy the Excel workbook file to the USB drive.

  7. 7.

    Disconnect the USB drive and connect it to the second computer. After Windows recognizes and configures the drive, open Windows Explorer and paste the workbook file from the USB drive to a folder on the second computer. Launch Microsoft Excel on the second computer and use the macro-enabled workbook as you normally would.

Copy Macros with the VBA Editor

  1. 1.

    Launch Excel on the first computer. Open the Excel workbook file or template with macros you want to export to another computer.

  2. 2.

    Press the “Alt-F11” keys to launch the Visual Basic for Applications, or VBA, editor.

  3. 3.

    Double-click the “Modules” folder in the VBAProject pane on the left side of the VBA editor window. Highlight the name of the first module under the Modules folder header.

  4. 4.

    Click “File” on the VBA editor toolbar, and then click “Export File.” Enter a name for the module name in the “File Name” field or leave the default name in place. Select a folder in which to save the module file and click “Save.”

  5. 5.

    Export other macro modules listed under the Modules folder header to your selected folder.

  6. 6.

    Connect a USB drive to your computer. Copy and paste the .BAS module files created by the VBA editor to the USB drive.

  7. 7.

    Connect the USB drive to the second computer. Launch Microsoft Excel and open the workbook file or template in which you want to place the macro modules.

  8. 8.

    Press “Alt-F11” to open the VBA editor. Click “File” and then “Import File” on the menu bar. Browse to the folder on the USB drive where you saved the .BAS files from the first computer. Highlight the first module file name and then click “Open.” The VBA editor imports the module code and creates a new macro in the current workbook with the macro name used on the first computer.

  9. 9.

    Import the other macro modules from the USB drive using the VBA editor.

  10. 10.

    Click “File” and then “Save As.” Save the current workbook as an Excel Macro-Enabled Workbook. Use the macros in the workbook as you normally would.

    Excel We sometimes need to perform the same thing or use the same formula in our Excel workbooks. In this regard, we can automate our work by enabling macros. By enabling macros, we will have to write a code once. And, the great thing is that we can use the code as many times as we want if we can save the macro in Excel forever. It would save our time and energy. In this article, I will show you 2 suitable ways to save macros in Excel permanently.


    Table of Contents hide

    Download Practice Workbook

    What Is a Macro?

    2 Suitable Methods to Save Macros in Excel Permanently

    1. Save Macros Permanently as a Part of All Excel Workbooks Using XLSB Format

    2. Save Macros Permanently as XLAM Format

    Things to Remember

    Conclusion

    Related Articles

    Download Practice Workbook

    You can download and practice from our workbook here.

    Save Macros Forever.xlsm


    What Is a Macro?

    Macro is a set of instructions written in VBA ( Visual Basic for Applications) code. You can fix a guideline for a definite performance or formula or calculation using macro. The advantage of a macro is you can perform a series of actions with a single click as many times as you want. You have to write the VBA code in the Visual Basic Editor. You can find it in the Developer tab >> Visual Basic section.

    Can macros be saved in any Excel file?


    2 Suitable Methods to Save Macros in Excel Permanently

    Suppose, we have an Excel file, where we have to put the copyright sign frequently. So, we can write a VBA code in the macro for copyright signs and use it frequently wherever we need it. If we want to use it in every workbook, we need to save it permanently.  Follow any of the two ways described below to save macro permanently in Excel.

    Can macros be saved in any Excel file?


    1. Save Macros Permanently as a Part of All Excel Workbooks Using XLSB Format

    You can save a macro permanently in Excel using the .xlsb format. Follow the steps below to accomplish this. 👇

    📌Steps:

    • First, go to the Developer tab. Subsequently, click on the Record Macro tool.

    Can macros be saved in any Excel file?

    • As a result, the Record Macro window will open. Now, write the macro name in the Macro name: text box. Subsequently, choose the Personal Macro Workbook option from the Store macro in: dropdown list. Finally, click on the OK button. Besides, you can also declare a shortcut for running the macro.

    Can macros be saved in any Excel file?

    • Now, close the workbook. A Microsoft Excel warning dialogue box will pop up. Subsequently, click on the Save button. Thus, you have saved your macro permanently.

    Can macros be saved in any Excel file?

    • Now, your personal macro file is ready, but there is no code inside it yet. For doing this, open the workbook again and go to the Developer tab. Subsequently, click on the Visual Basic tool.

    Can macros be saved in any Excel file?

    • Now, at the project window, you can see there is a project named PERSONAL.XLSB. Double-click on the project and choose Module 1 to write the code. Subsequently, write the code in the visual basic editor.

    Can macros be saved in any Excel file?

    • Subsequently, close and save the Excel file to save the code in the macro.
    • Now, open the workbook again and go to the Developer tab. Subsequently, click on the Macros tool.

    Can macros be saved in any Excel file?

    • As a result, The Macro window will appear now. Subsequently, choose the macro and click on the Run button.

    Can macros be saved in any Excel file?

    • As a result, the code will run and put the copyright sign in the active cell.

    Can macros be saved in any Excel file?

    Finally, you can use this permanent macro in an Excel workbook and run it at any cell you want.

    Can macros be saved in any Excel file?

    Read More: How to save workbooks that contain Macros


    Similar Readings

    • How to Save Excel File as CSV (3 Handy Methods)
    • How to Save Multiple Excel Sheets as One File (5 Easy Methods)
    • [Fixed!] Document Not Saved Excel Network Drive (5 Possible Solutions)
    • Excel VBA: Save and Close Workbook (5 Suitable Examples)
    • How to Save Excel as PDF Fit to Page (5 Easy Ways)

    2. Save Macros Permanently as XLAM Format

    Besides, you can save your macros permanently in Excel using the .xlam format. Follow the steps below to do this. 👇

    📌Steps:

    • First, go to the Developer tab. Next click on the Visual Basic tool.

    Can macros be saved in any Excel file?

    • As a result, the Microsoft Visual Basic for Applications window will open. Now, click on the Insert tab. Subsequently, choose the Module option from the dropdown list.

    Can macros be saved in any Excel file?

    • As a result, a new module named Module 1 under the Modules group is created. Click on Module 1 and subsequently, write the code in the VBA editor.

    Can macros be saved in any Excel file?

    • Now, click on the File tab.

    Can macros be saved in any Excel file?

    • Subsequently, click on the Save As option from the expanded File tab.

    Can macros be saved in any Excel file?

    • At this time, The Save As window will appear. Choose the .xlam format as the save as type from the Save as type: options. Next, click on the Save button.

    Can macros be saved in any Excel file?

    • Now, your macro is saved permanently as an add-in in Excel. Now, for checking, open any workbook and go to the File tab >> click on More… >> click on Options

    Can macros be saved in any Excel file?

    • The Excel Options window will open now. Choose the Add-ins from the tab list on the left side. Choose your saved add-in from the add-in list. Click on the Go button.

    Can macros be saved in any Excel file?

    • As a result, the Add-ins window will open. Choose your add-in and click on the OK button.

    Can macros be saved in any Excel file?

    Now, finally, your saved add-in is installed in the current workbook and you can put the copyright sign using the code at any cell you want in any workbook just like the 6th,7th, and 8th steps of the first method. And, after running the code in several cells, the result will look like this. 👇

    Can macros be saved in any Excel file?

    Read More: How to Save Macro to Personal Macro Workbook?


    Things to Remember

    To enable macro in an Excel file, it is a must to save the Excel file in .xlsm format. If you do not save Excel in this format, the macro will not work. And another thing is saving in this format will enable the macro in the current workbook only. If you want to save the macro permanently and use it in every workbook, you will have to follow any of the 2 ways described here.


    Conclusion

    So, in this article, I have shown you 3 suitable ways to save macros permanently in Excel in brief. Use any of these effective methods to accomplish your result in this regard. I hope you find this article helpful and informative. If you have any further queries or recommendations, please feel free to contact me. And, visit ExcelDemy for many more articles like this. Thank you!

    Can I use macro in different Excel files?

    If a workbook contains a Visual Basic for Applications (VBA) macro that you would like to use elsewhere, you can copy the module that contains that macro to another open workbook by using the Visual Basic Editor (VBE).

    Can macros be stored in a standard Excel workbook?

    But what if you want to use the macro in other workbooks? To make your macros available every time you open Excel, you can create them in a workbook called Personal. xlsb. That's a hidden workbook stored on your computer, which opens in the background every time you open Excel.

    Which workbook formats allow macros?

    Understanding which file types allow macros.
    Excel Workbook (. xlsx)—Files are stored as a series of XML objects and then zipped into a single file. ... .
    Excel Macro-Enabled Workbook (. xlsm)—This is similar to the default . ... .
    Excel Binary Workbook (. ... .
    Excel 97-2003 Workbook (..