How to Use a Macro to Save an Excel Spreadsheet

Kate Bluest

Creating and using a macro to save an Excel sheet is a great way to use a simple task to teach yourself how to create a macro. An Excel macro is a recorded series of steps that can be triggered by a keyboard shortcut or toolbar icon. Before you can use a macro to save an Excel spreadsheet, you must create a macro to perform this function. Creating a macro is an advanced Excel action available on the Developer tab.

Open Microsoft Excel. If the Developer tab is visible at the right end of the top menu, click the "Developer" tab, and skip the next step. If the "Developer" tab is not visible, go to Step 2.

Click the "Microsoft" icon and click the "Excel options" button in the lower-right corner of the sub-menu to open the "Excel Options" dialogue box. Click "Popular" in the left menu and place a check in the check box next to "Show Developer tab in the Ribbon" in the "Top options for working with Excel" section of the "Popular" page in the "Excel Options" dialogue box. Click the "OK" button to close the dialogue box and apply the change. Click the "Developer" tab.

Click the "Record Macro" icon in the "Code" section of the "Developer" tab to open the "Record Macro" dialogue box.

Type a name for your macro in the "Macro name" text box in the "Record Macro" dialogue box. Assign a letter to be used with the "Ctrl" key as a keyboard shortcut for your macro. Select "This Workbook" from the pull-down list in the "Store macro in" section and enter a description for the macro in the "Description" field. Click the "OK" button to close the "Record Macro" dialogue box and begin to record the action the macro will perform.

Click the "Save" icon in the "Quick Access" toolbar located on the left side of the title bar to save the document. This action will open the "Save As" dialogue box. Enter a file name in the "File name" text box, Click the "Save as type" pull-down arrow and select "Excel Macro-Enabled Workbook (*xlsm)" and click the "Save" button.

Click the "Stop Recording" icon in the "Code" section of the "Developer" tab.

Hold down the "Ctrl" key and select the letter you chose in the "Record Macro" dialogue box to use the saved macro.

Tip

Perform this action to change the letter associated with your macro or to remind yourself of which letter you chose: Click the "Macros" icon in the "Code" section of the "Developer tab to open the "Macro" dialogue box. Select your macro from the list and click the "Options" button to open the "Macro Options" dialogue box. Click "OK" when you are done to return to the "Macro" dialogue box. "Click "Run" to run the macro and close the "Macro" dialogue box or "Cancel" to close the dialogue box. Hold down the "Ctrl" key and press the "S" key to save a document. Use the "Save" icon in the Quick Access menu of the title bar to save a document. Click the "Microsoft" icon in the upper right corner and select "Save" or "Save As" to save a document.

Warning

Choose carefully. Do not use letters that are used for other shortcuts because if you do, your macro will override the standard shortcut for the key combination. For example, if you use "n," you would no longer be able to use "Ctrl+n" to open a new document.

Resources

About the Author

Based in New York, Kate Bluest has been writing for various online publications since 2005. She has participated in several writing workshops, including the MIT Writing Workshop. Bluest holds a Bachelor of Science in business administration from SUNY Empire State College.