Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel xp macros cannot be saved to personal.xls | Excel Discussion (Misc queries) | |||
Can't delete Excel file saved as web page | Excel Discussion (Misc queries) | |||
I keep losing saved Excel macros, what am I doing wrong? | New Users to Excel | |||
Why does macros assigned to a toolbar lose its link when saved as | Excel Discussion (Misc queries) | |||
How can I see a copy of a saved workbook before I saved it again? | Excel Worksheet Functions |