Written by Allen Wyatt (last updated October 17, 2020)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and 2021
Gerald asked if there was a way to get rid of all the macros in an Excel workbook, without the need to individually delete them. There are three ways you can accomplish this task. The first (and easiest) method is to simply use Save As (press F12) and use the Save As Type drop-down list to specify that you want to save the workbook in XLSX format. This approach removes all macros as they cannot be saved in this workbook format.
The second approach is essentially a variation on the "new workbook" approach:
Figure 1. The Move or Copy dialog box.
Your worksheets have now been moved to a new workbook—one that does not have any macros attached to it. You can now save the new workbook using any file format you desire.
The third approach is to simply work with the existing workbook and is a viable choice if you feel comfortable with macros in the first place. Follow these steps:
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (10656) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and 2021. You can find a version of this tip for the older menu interface of Excel here: Removing All Macros.
Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!
Need to know the address of the cell that is currently selected? The function and macro highlighted in this tip will come ...
Discover MoreNamed ranges are a great capability provided by Excel. You can define all sorts of named ranges in a workbook, but how do ...
Discover MoreWhen you add a new worksheet to a workbook, Excel gives it a default name that consists of "Sheet" followed by a number. ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2020-10-19 02:39:06
Alex B
I also don't know why you would use any method other that the 1st one.
If you went with option 2 to copy the sheets, an additional problem you would encounter is that you would have to copy each sheet that included a table individually (or use Ron De Bruin's copy sheets macro).
2020-10-17 17:53:24
Roy
@Willy: Even if you don't want to change the extension, just Save As the .xlsx then Save As back over the .xlsm. Delete, probably, the .xlsx (depending upon why you wanted the clean of macros file, and it's all over. No questions asked.
2020-10-17 09:22:45
Alan Cannon
All macros are not stored in modules. Some can be stored in a worksheet's code "window" and some in the workbook "window".
2020-10-17 06:35:19
Willy Vanhaelen
Saving the file as an .XLSX file is the only method that will for sure remove all macros.
The other two approaches suppose there are only macros in modules but each worksheet or chartsheet has its own code page that can contain macros too especially event macros. The ThisWorksheet code page can have macros as well. They all remain there unless you delete them manually which can be tedious.
The third approach can only be useful if you really don't want to change the extension but then take care of checking the sheet code pages.
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Comments