Macro to delete sheets and saves remaining file does not properly delete module

Joined
Jun 21, 2005
Messages
3
Reaction score
0
I have a macro that has the user save the file through a command button rather than the regular save. This macro checks for data entry errors, deletes sheets that were not selected for data entry, and saves the remaining file.

However, the sheet module from the deleted sheets does not get deleted (when I say delete, I do not mean that I want the code in the module to be blanked; I mean that the module should no longer appear in VBE just as the sheet does not appear in the Excel interface).

Simplified code:

Private Sub deleteandsave()
Sheet1.Delete
ThisWorkbook.Save
End Sub

If you look at VBE after running this, the sheet module for Sheet1 does indeed get deleted. However, the file that was actually saved maintains the sheet module although the worksheet itself was deleted. This in effect produces a file with a sheet module that is linked to the "ether". This unlinked sheet module can have strange behavior on the rest of the file even if blank.

Any suggestions that makes sure that the module gets deleted before the save command is run? (I prefer that Save is not a separate action - If you delete sheets and then Save as two independent actions - two buttons - the unlinked sheet module does not occur)
 
Joined
Jun 21, 2005
Messages
21
Reaction score
0
if the sheet has code which is being run from, then it will have to find somewhere for it to store the code for the rest of the procedure to work. What you need to do, is move it to a module or in the This workbook section.
 
Joined
Jun 21, 2005
Messages
3
Reaction score
0
This happens even if the deleted sheet had no code. You end up with a file that deletes Sheet1, but has a blank Sheet1 module - what I need is for the Sheet1 module to not appear as a VBE object at all.
 
Joined
Jun 21, 2005
Messages
3
Reaction score
0
Did not work,

This still creates a saved file that has no Sheet1 worksheet, but maintains its module as a VBE object. I know for a fact that the procedure does delete the module, but only at the end of the macro. The module disappears from the VBE, but the file had already being saved. If I save a second time, the module is then gone in the saved file, but I don't want to ask the user to save twice.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top