Delete all macros if workbook is saved with another name

[Guest]

If there a way to delete all the macros if the workbook is saved with another
name?

Thanks,

Danny

 

[Ron de Bruin]

Hi Danny

Only with VBA code or if there is no code in the sheet modules you can do this

Right click on a sheet tab and choose "Select all sheets"
Right click again and choose "Move or copy"
In the "to book" list choose new workbook
check "create a copy"

You have now a new workbook with all the sheets

 

[Guest]

Hi Ron,

Can you please provide me with the VBA code? I'd appreciate it very much.

I got this macro from this NG. However, I don't know how to write the VBA
code "IF" the workbook is saved another another name.

Sub Delete_All_Macros()

On Error Resume Next

With ActiveWorkbook.VBProject.VBComponents
.Remove .Item("Module1")
.Remove .Item("Module2")
.Remove .Item("Module3")
.Remove .Item("Module4")
.Remove .Item("Module5")
.Remove .Item("Module6")
.Remove .Item("Module7")
.Remove .Item("Module8")
.Remove .Item("Module9")
.Remove .Item("Module10")
With .Item("ThisWorkbook").CodeModule
.DeleteLines 1, .CountOfLines
End With
End With
End Sub


Thank you and have a great weekend.

 

[Ron de Bruin]

Hi Danny

or if there is no code in the sheet modules you can do this

Sheets.copy

This code line create a new workbook with all sheets.
But if there is also code in the sheet modules you can use the example below :


You can delete the code in your sheet modules with Chip's code
http://www.cpearson.com/excel/vbe.htm

This is working OK for the activeworkbook
No reference needed in the example

Read the note on Chip's site about "Trust access to Visual Basic Project"


Public Sub DeleteAllVBA()
Dim VBComp As Object
Dim VBComps As Object
Set VBComps = ActiveWorkbook.VBProject.VBComponents
For Each VBComp In VBComps
Select Case VBComp.Type
Case 1, 3, _
2
VBComps.Remove VBComp
Case Else
With VBComp.CodeModule
.DeleteLines 1, .CountOfLines
End With
End Select
Next VBComp
End Sub

 

[Guest]

Thanks a lot Ron.

Hi Danny


Sheets.copy

This code line create a new workbook with all sheets.
But if there is also code in the sheet modules you can use the example below :


You can delete the code in your sheet modules with Chip's code
http://www.cpearson.com/excel/vbe.htm

This is working OK for the activeworkbook
No reference needed in the example

Read the note on Chip's site about "Trust access to Visual Basic Project"


Public Sub DeleteAllVBA()
Dim VBComp As Object
Dim VBComps As Object
Set VBComps = ActiveWorkbook.VBProject.VBComponents
For Each VBComp In VBComps
Select Case VBComp.Type
Case 1, 3, _
2
VBComps.Remove VBComp
Case Else
With VBComp.CodeModule
.DeleteLines 1, .CountOfLines
End With
End Select
Next VBComp
End Sub