Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Removing All Macros.

Removing All Macros

Written by Allen Wyatt (last updated February 2, 2021)
This tip applies to Excel 97, 2000, 2002, and 2003


8

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 two ways you can accomplish this task. The first approach is used if you don't want to mess with the macros at all. Just follow these steps:

  1. Unhide any worksheets that may be hidden.
  2. Select all the worksheets in the workbook. (Click on the first worksheet tab, then hold down Shift as you click on the last worksheet tab.)
  3. Right click on one of the worksheet tabs. Excel displays a Context menu.
  4. Choose Move or Copy from the Context menu. Excel displays the Move or Copy dialog box. (See Figure 1.)
  5. Figure 1. The Move or Copy dialog box.

  6. Using the To Book drop-down list, choose (new book).
  7. Make sure the Create Copy check box is not selected.
  8. Click on OK.
  9. Rehide any worksheets you unhid in step 1.

Your worksheets have now been moved to a new workbook—one that does not have any macros attached to it.

The second 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:

  1. Press Alt+F11 to display the VBA Editor.
  2. In the Project Explorer (upper-left corner of the Editor), right-click on a module that you want to delete. (Remember that macros are stored in modules, and that you should only right-click on a module that is associated with the workbook that you want to cleanse.) Excel displays a Context menu.
  3. Choose the Remove option from the Context menu. The actual wording of the option will include the name of the module you want to remove, such as Remove Module1.
  4. When asked if you want to export the module before removing it, click on No.
  5. Repeat steps 2 through 4 for any other modules you want to remove.
  6. Close the VBA Editor.

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2713) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Removing All Macros.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Can't Save Edited Document

Each day of using Word is filled with opening documents, editing them, and then saving those changes to disk. It can be ...

Discover More

Determining "Highest Since" or "Lowest Since"

When compiling statistics on a collection of data points, you may want to know whether a particular value is the "highest ...

Discover More

Superscripts in Find and Replace

The find and replace used in Excel is less powerful than its counterpart in Word, so it is not able to do some of the ...

Discover More

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!

More ExcelTips (menu)

Renaming a Macro

Got a macro that doesn't have quite the right name? You can rename the macro by following these simple steps.

Discover More

Stepping Through a Non-Contiguous Range of Cells

Using macros to step through each cell in a selection is a common occurrence. What if that selected range is made up of ...

Discover More

Transferring Data between Worksheets Using a Macro

Macros can be used for all sorts of data processing needs. One need that is fairly common is the need to move data from ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is five minus 3?

2020-07-04 02:55:44

Peter

Hi,
I have found that if you copy a worksheet to a new book (using a macro) that the code associated with that sheet goes with it.
It is cumbersome, but if I save that sheet to an .xlsx workbook the close and reopen, I can purge the macros.

Moving the sheet manually does not remove associated macros either.

Is there a way to remove macros with VBA?


2020-07-03 12:15:04

Ram

Hi, I have worksheet with some code (Not Module) and i copied the sheet. While copying its the vba code also got copied along with the sheet but i dont want the code in newly created sheet. Please help to delete code available in newly created sheet.


2019-01-04 22:41:39

Frank Aquino

Thanks Allen, your first method (ie move worksheets to new workbook) solved a problem that seems otherwise unsolvable on the internet, which is the "Excel cannot save due to sharing violation" problem. It seems that when a workbook reaches a certain size, or contains a certain amount of formatting or macros or something other than basic numbers and formulas, it simply will not save the workbook anymore. Instead it saves a stripped down version in a temp file minus all formatting, zero suppression, embedded images and all the other nice attributes we've come to know and love. The error message says it's due to a sharing violation, but that's not true. It happens on a stand alone, unshared, isolated, one administrator machine just as often as on a shared machine. My gut feel is there is a limit of attributes beyond which excel can't cope. In my case it was dozens and dozens of macros which would have taken forever to delete one by one.

Thanks again, it was very helpful.
PS If there is a cast-iron solution to the sharing violation problem, I'd love to hear it.


2017-03-08 13:41:09

Essa

Thanks ... that is Help me .


2017-02-25 05:35:51

Petros

Simon

You may need to remove the code signature using a VBE menu, before removing all VBA code. I guess your code certificate is still valid

The Macro Mover add-in removes the entire VBA project, so there is chance of 'left-overs'. However, it does not remove signatures. Thanks, that is a feature I will add in the near future!

If you do not want your colleagues to tamper with your VBA, you can lock your code as unviewable.

http://www.spreadsheet1.com/unviewable-vba-project-app-for-excel.html


2017-02-24 06:49:39

Simon

Hello, in my company we've set the security option, that only signed macros are allowed. Normaly, all macros which i've tested are working fine, when I attach a signature, but one document is putting me into trouble. Even if I attach a signature, I get a message, that makros are disabled for this project.
I spent a lot of time for troubleshooting and got the idea, that there can be some kind of code, which is not included in the signature, so that excel denys makros for this document in general. When I completely remove all macros with your second method and go to "check document for problems" (translated from my german installation, in english hopefully equal), the I get a message, that there is still some code inside. I think that this message is supporting my idea, that there is some code anywhere in the document, which I'm not able to find.

Do you have any suggestions for me to solve my problem?

By the way, your first method cannot work for my end scenario, because there the excel sheet is called from SAP and I cannot import a completly new generated document. I only can modify this dump document, which is not working with a signature.


2016-10-29 09:53:32

allen@sharonparq.com

Petros is correct, but he is providing an answer for newer versions of Excel. Remember that *this tip* (above) is for older versions of Excel (2003 and before) where you CANNOT save in a "macro-free format." For the older versions, you must follow the steps listed above.

If you are using a newer version of Excel (2007 and later), then follow the link at both the beginning and end of the tip, above, to find the proper instructions you need.

-Allen


2016-10-29 09:48:55

Petros

The easiest way to remove all VBA code from a macro-enabled Office file is to save the file in a macro-free format. For example, save a .xlsb workbook as .xlsx

However you should not open any workbook that you suspect that it has malicious macros or code with bugs that may damage your data or PC. If you disable macros, then you are left with no choice other than to delete macros manually or open and save each file in a macro-free format.

However, the Macro Mover add-in allows you to mitigate security risks and work more productively:
Macros are removed from closed files only! There is no need to disable all macros from the Trust center. Your files will not be opened in Excel, so VBA code cannot be executed.

Code is deleted from closed files, so you don't have to wait while opening/saving large files.

VBA code can be removed from any macro-enabled Office file (including add-ins), even from locked VBA projects. 

Macros can be removed without changing the extension of the file. You can still take advantage of the high compression ratio offered by the Excel binary file format (.xlsb), while erasing macros fast.

Macros can be removed from multiple Office files (Excel, PowerPoint, Word) in a single operation.

Read more:

http://www.spreadsheet1.com/move-excel-vba-projects-from-one-workbook-to-another.html


This Site

Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.