XL2001: Copying Sheets May Cause Macro Alert to Appear (286265)

WORKAROUND

To work around this problem, review the cause and pick the method below most appropriate to the cause and your needs.

NOTE: Microsoft recommends that you do not turn off Macro Virus Protection to resolve this problem. If sheets are copied into a new workbook or a workbook that contains no macros and the file is saved, you will not receive the warning.

If you need the macro code and button, see the "Move the Code" section of this article.

Method 1: Delete Button Reference, Button, Macro Definition, Macro Sheet or Code

If you have an XL4 type macro sheet in the workbook, you can resolve the problem by removing the macro definitions, and deleting all macro sheets. If a button has referred to the XL4 type macro, you also need to remove the reference to that macro from the button or remove the button. All three are needed to resolve the problem.

If you have Visual Basic for Applications macro code, you can resolve the problem by removing the reference to that macro from the button, or removing the button.

Choose the appropriate methods from the following.

Method 1A: Delete Button Reference

Remove the button reference by using the following steps:

1. With the CONTROL key pressed, click the button or object, and click Assign Macro on the shortcut menu.
2. In the Macro name edit box, with the name of the macro highlighted, click the DELETE key.
3. With the Macro name edit box clear, click OK. NOTE: Visual Basic for Applications macro code without the button reference should not cause the problem.

Method 1B: Delete the Button

If you do not need or want the button, you can delete the button. To do this, follow these steps:

1. With the CONTROL key pressed, click the button or object to select it.
2. On the Edit menu click Clear and All.

Method 1C: Delete the Macro Definition

With macro sheets, remove the macro definition. To do this, follow these steps:

1. On the Insert menu click Name and then click Define.
2. For each name in the workbook that defines a macro, select it and click Delete.
3. Click OK to close the dialog box.NOTE: If you have XL4 type macro sheets, to resolve this problem, you must remove the macro definitions, delete all macro sheets, and delete any reference to a macro in a button or object.

Method 1D: Delete XL4 Macro Sheet

If you need the macro code, see the "Move the Code" section.

1. Click the tab of the macro sheet.
2. On the Edit menu, click Delete Sheet.
   
   You receive the following warning:

   The selected sheet(s) will be permanently deleted.

3. Click OK.

Delete Visual Basic for Applications Macro Code or Module

Although it is not necessary to remove Visual Basic for Applications macro code or modules to resolve this problem, you can remove Visual Basic for Applications code. To do this, follow these steps:

1. On the Tools menu, click Macro, and then click Visual Basic Editor (or press OPTION+F11 on some systems) to start the Visual Basic Editor.
2. Find the Project - VBA Project window. If it is not visible, click Project Explorer on the View menu.
3. In the Project - VBA Project window select the workbook project with the code and click the triangle on the left to turn it down and view the objects and modules in the project.
4. Click on the triangle on the left of the Modules folder to turn it down and show individual modules.
5. Double-click on a module to view the code in a code window. If you want to remove the code, highlight all of the code in a subroutine and press DELETE to remove it.
6. If you want to remove the whole module, click the File menu and select Remove Module (listed by module name such as "Module1"), and when prompted, click Yes to delete the module.
7. Do this with all modules in which you want to clear the code or remove the module.
8. When you are finished, on the File menu click Close and Return to Microsoft Excel and then save the file.

Method 2: Move the Code

If you need the code and/or button and want to avoid the problem by moving the code or macro sheet to a different workbook, use the most appropriate of the following methods.

Method 2A: Move Macro Sheet to a Different Workbook

If the problem is caused by macro code in a Macro sheet, move the Macro sheet to another workbook. Redefine the macro in that workbook, and remove the macro definition in the current workbook. For additional information about steps to move a macro sheet, click the article number below to view the article in the Microsoft Knowledge Base: If you have buttons or objects assigned to the run macro code, reassign them to run the code in the new location.

Method 2B: Move Module Code to a Different Workbook

If the problem is caused by a button or object assigned to run Visual Basic for Applications macro code in modules, follow these steps:

1. Move the code referred to by the button or object to another workbook.
2. Reassign the button or object to run the code in the new location.

For additional information about steps to move module code, click the article number below to view the article in the Microsoft Knowledge Base:

Method 3: Use Visual Basic Code

Because Visual Basic code does not cause this warning, create Visual Basic for Applications macro code in a different workbook to copy sheets in the active workbook. For additional information about copying sheets with code, click the article number below to view the article in the Microsoft Knowledge Base: NOTE: The code needs to be in a separate workbook. This is because of a problem in Excel 2001 when the Visual Basic for Applications code that is used to copy sheets is in the same workbook as the sheets being copied. For additional information about the problem copying worksheets with code in the same workbook, click the article number below to view the article in the Microsoft Knowledge Base: