Delete all macros if workbook is saved with another name
If there a way to delete all the macros if the workbook is saved with another
name? Thanks, Danny |
Delete all macros if workbook is saved with another name
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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Danny" wrote in message ... If there a way to delete all the macros if the workbook is saved with another name? Thanks, Danny |
Delete all macros if workbook is saved with another name
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" wrote: 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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Danny" wrote in message ... If there a way to delete all the macros if the workbook is saved with another name? Thanks, Danny |
Delete all macros if workbook is saved with another name
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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Danny" wrote in message ... 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" wrote: 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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Danny" wrote in message ... If there a way to delete all the macros if the workbook is saved with another name? Thanks, Danny |
Delete all macros if workbook is saved with another name
Thanks a lot Ron.
"Ron de Bruin" wrote: 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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Danny" wrote in message ... 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" wrote: 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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Danny" wrote in message ... If there a way to delete all the macros if the workbook is saved with another name? Thanks, Danny |
All times are GMT +1. The time now is 10:17 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com