Excel Macro to delete the Macro?

I would imagine control macro A which calls macro B.  Where macro B is what you want to delete and the last item in macro A for instance deletes macro B or module containing macro b.

See http://www.cpearson.com/excel/vbe.aspx for deletion of vba modules/code etc.

Chris

Something like this might work. This needs to go in a module named "Module1"

Option Explicit

Sub SaveAsWithoutVB()
    Dim lLineCount As Long
   
ThisWorkbook.SaveAs (ThisWorkbook.Path & "\test2")

With ActiveWorkbook
    lLineCount = .VBProject.vbcomponents.Item("Module1").codemodule.countoflines
    .VBProject.vbcomponents.Item("Module1").codemodule.DeleteLines 1, lLineCount
    .Save
End With

End Sub

Hi
The macro is already in module 1, so does your extra bit need to come after that?

Or before my current macro's End Sub?

This is what my current macro looks like.... where do I add the extra parts?

Sub myRefresh()
 
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
 
    ActiveWorkbook.RefreshAll
 
    Sheets(Array("MONTH Data", "MONTH <65", "MONTH 65+")).Select
    
    Sheets("MONTH Data").Activate
    Cells.Select
    Cells.EntireColumn.AutoFit
    
    Range("A1").Select
    
    Sheets("Macro").Select
    Range("A5").Select
 
    Sheets("REPORT").Select
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
    Sheets("MONTH Data").Select
    Application.CutCopyMode = False
    ActiveWindow.SelectedSheets.Delete
     
    Sheets("REPORT <65 Years").Select
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
    Sheets("MONTH <65").Select
    Application.CutCopyMode = False
    ActiveWindow.SelectedSheets.Delete
    
    Sheets("REPORT 65+ Years").Select
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
    Sheets("MONTH 65+").Select
    Application.CutCopyMode = False
    ActiveWindow.SelectedSheets.Delete
    
    Sheets("MACRO").Select
    Range("A1").Select
    
    ThisFile = Range("B5").Value
    ActiveWorkbook.SaveAs Filename:=ThisFile
    Sheets("Macro").Select
    ActiveWindow.SelectedSheets.Delete
    
    ActiveWorkbook.Save
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
                
End Sub

Select allOpen in new window

See below.... have added the code.

it does delete Module 1, but when I open the resultiung file....I still get that enable/disable macro warning.????

    Sheets("MACRO").Select
    Range("A1").Select
    
    ThisFile = Range("B5").Value
    ActiveWorkbook.SaveAs Filename:=ThisFile
    Sheets("Macro").Select
    ActiveWindow.SelectedSheets.Delete
    
    ActiveWorkbook.Save
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    
Dim lLineCount As Long
    
With ActiveWorkbook
    lLineCount = .VBProject.vbcomponents.Item("Module1").codemodule.countoflines
    .VBProject.vbcomponents.Item("Module1").codemodule.DeleteLines 1, lLineCount
    .Save
End With
 
    ActiveWorkbook.Save
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
 
End Sub

Select allOpen in new window

hmm, quite odd.

How many users are we talking about? Would you be prepared to turn the macro warning 'off' on each computer?

Jell

If you don't have any code behind the worksheets themselves, you could simply copy the sheets to a new workbook and save that - then the module won't go with it.
Regards,
Rory

Chris is right about needing to delete the module not just the lines of code contained therein in order to prevent the macro warning from appearing.

One other alternative -- and I realize that what I'm suggesting requires a non-trivial upgrade decision: you could upgrade to Excel 2007. With 2007, the default file type (*.xlsx instead of *.xls) doesn't allow macros, i.e., you can't even save a "normal" spreadsheet when there is macro code in it. You are required to save as a new macro-enabled file type, *.xlsm if you want macros to work. Consequently, if you save your spreadsheet as *.xslx, there will be no active macro code.