JohnGow383
Board Regular
- Joined
- Jul 6, 2021
- Messages
- 141
- Office Version
- 2013
- Platform
- Windows
Hello,
I have a simple macro which loops through all worksheets in the workbook and then saves them in a chosen directory as seperate workbooks and then closes those after running. The files are saved as macro free workbooks. The macro is fired from a command button located in each sheet of the original workbook however, when saving them as macro free workbooks, the command button is also being saved in the new macrofree workbooks.
Does anyone know of a way for this not to save the command button? I guess I could do a loop to ensure the shapes are deleted prior to closing and saving the workbooks but this would be dangerous if I had other workbooks open i the background that contain command buttons. Here is my code for saving and closing.
and for closing
Thanks in advance
I have a simple macro which loops through all worksheets in the workbook and then saves them in a chosen directory as seperate workbooks and then closes those after running. The files are saved as macro free workbooks. The macro is fired from a command button located in each sheet of the original workbook however, when saving them as macro free workbooks, the command button is also being saved in the new macrofree workbooks.
Does anyone know of a way for this not to save the command button? I guess I could do a loop to ensure the shapes are deleted prior to closing and saving the workbooks but this would be dangerous if I had other workbooks open i the background that contain command buttons. Here is my code for saving and closing.
VBA Code:
Sub SaveEachSheetsAsWB() 'Loop through each sheet and save as a seperate workbook
Dim ws As Worksheet
Dim wb As Workbook
Dim Path As String
Path = "P:\02 STORES\Send for Quotation\"
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each ws In ThisWorkbook.Worksheets
Set wb = Workbooks.Add
ws.Copy Before:=wb.Sheets(1)
wb.SaveAs Filename:=Path & ws.Name & ".xlsx", FileFormat:=51
Set wb = Nothing
Next ws
SaveCloseOtherWBs
Application.DisplayAlerts = True
Application.ScreenUpdating = True
MsgBox "All Stores Sheets are Saved as Seperate Workbooks located in " & vbNewLine & vbNewLine & "P:\02 STORES\Send for Quotation", vbOKOnly
End Sub
and for closing
VBA Code:
Sub SaveCloseOtherWBs() 'Saves and closes any other workbooks other than this workbook
Dim wb As Workbook
For Each wb In Workbooks
If wb.Name <> ThisWorkbook.Name Then
wb.Close savechanges:=True
End If
Next wb
End Sub
Thanks in advance