How to Remove Shapes from a Macro Free Workbook Using VBA

[JohnGow383]

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.

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

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

 

[JohnGow383]

I assume you need wb.sheets.

In which line?

For Each ws In wb.Sheets

?

 

[JohnGow383]

Thanks very much. That was the issue. Now it's working perfectly. Thanks again for helping me. I'll post the final solution here to help others.

I assume you need wb.sheet

 

[JohnGow383]

The final solution to remove command buttons when saving multiple sheets as seperate macro free workbooks of same name as their sheets is as follows:

Sub RemoveCmdButtons()

Dim shp As Shape, ws As Worksheet, wb As Workbook

For Each wb In Workbooks    'Loop through each open workbook
      If wb.Name <> ThisWorkbook.Name Then  'Except the source workbook with the macro - Ensures the source file isn't affected
   For Each ws In wb.Sheets  'Loop through each sheet in each workbook
        For Each shp In ws.Shapes  'Loop through each shape in each sheet of each workbook
           If shp.Name = "Name of Button Here" Then  'Added to specify one particular button - Rename the button so it's unique to avoid unwanted generic button names being removed in other open WBs
               shp.Delete  'Delete the button
                  End If
            Next shp
        Next ws
      End If
   Next wb
End Sub