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.
Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!
Do your macros seem to be disabled on your new machine? It could be because of the security settings in Excel. Here's ...
Discover MoreUnprotecting a single worksheet is relatively easy. Unprotecting a whole lot of worksheets is harder. Here's how you can ...
Discover MoreMacros are great for working with strings, and one of the most commonly used string functions is Len. This tip explains ...
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