Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Removing All Macros.
Written by Allen Wyatt (last updated October 17, 2020)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365
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 Excel in Microsoft 365. You can find a version of this tip for the older menu interface of Excel here: Removing All Macros.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!
The security features built into Excel allow you to digitally sign your macros so that users can rest assured that they ...
Discover MoreDo you get tired of the dialog box that says "do you want to enable macros" that is displayed when you open a workbook. ...
Discover MoreDoes your macro need to change the width of some columns in a worksheet? Here's how to do it.
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 © 2024 Sharon Parq Associates, Inc.
Comments