SAVE FILE IN NEW WORKBOOK AFTER DELETION OF ALL MACRO AND BUTTONS

[shahzeb123]

Currently, i am using below mentioned code to save file into new workbook after deletion of all macros.

However, in the main sheet there are button which is being saved in the new sheet.

What i want is to delete all the buttons, macros & pivot before saving the sheet.

Sub Buttonsave_Click()
    
    Dim Path As String
    
    
'Gets the name of the currently visible worksheet
    filename = Range("D5") & "-" & Format(Date, "dd-mmm-yy")
    
    'Copys the visible worksheet to a new workbook
    ThisWorkbook.ActiveSheet.Copy
    
    Cells.Copy
    
    Cells.PasteSpecial Paste:=xlPasteValues
    
    Path = "C:\Users\shakeelshahzeb\Desktop\Discounting\"
    
    'Saves the new Excel file
    ActiveWorkbook.SaveAs filename:=Path & filename & ".xlsx"
    
    'Closes the newly created workbook so you are still looking at the original workbook
    ActiveWorkbook.Close
End Sub

 

[Domenic]

You haven't specified whether the buttons are form controls or ActiveX controls. Also, I'm not sure whether you have only one pivot table or whether you have more than one. In any case, here are some sample code that you can use as a guide.

Deleting Form Control Buttons

To delete all form control buttons from the active sheet, you can use the following line...

    ActiveSheet.Buttons.Delete

Deleting ActiveX Command Buttons

If you don't have any other ActiveX controls or embedded objects, you can delete all command buttons using the following line...

    ActiveSheet.OLEObjects.Delete

Otherwise, you'll need to loop through each OleObject object, and check whether the object is a command button before deleting it...

    Dim currentOleObject As OLEObject
    For Each currentOleObject In ActiveSheet.OLEObjects
        If TypeName(currentOleObject.Object) = "CommandButton" Then
            currentOleObject.Delete
        End If
    Next currentOleObject

Deleting PivotTables

To delete a single pivot table when it's the only pivot table that exists, you can use the following line...

    ActiveSheet.PivotTables(1).TableRange2.Clear

If you want to delete any and all pivot tables, you'll need to loop through each pivot table, and then delete them one by one...

    Dim currentPivotTable As PivotTable
    For Each currentPivotTable In ActiveSheet.PivotTables
        currentPivotTable.TableRange2.Clear
    Next currentPivotTable

Converting the Data into Values

As long as you make sure that any and all pivot tables are deleted first, you can convert your data into values using the following, somewhat more efficient, method......

    With ActiveSheet.UsedRange
        .Value = .Value
    End With

Putting It All Together

Here's an example as to how you can put all of the above code together...

Sub Buttonsave_Click()
  
    Dim Path As String
    Path = "C:\Users\shakeelshahzeb\Desktop\Discounting\"
  
    'Gets the name of the currently visible worksheet
    Dim Filename As String
    Filename = Range("D5") & "-" & Format(Date, "dd-mmm-yy")
  
    'Copys the visible worksheet to a new workbook
    ThisWorkbook.ActiveSheet.Copy
  
    'Delete ActiveX command buttons
    Dim currentOleObject As OLEObject
    For Each currentOleObject In ActiveSheet.OLEObjects
        If TypeName(currentOleObject.Object) = "CommandButton" Then
            currentOleObject.Delete
        End If
    Next currentOleObject
  
    'Delete pivot tables
    Dim currentPivotTable As PivotTable
    For Each currentPivotTable In ActiveSheet.PivotTables
        currentPivotTable.TableRange2.Clear
    Next currentPivotTable

    With ActiveSheet.UsedRange
        .Value = .Value
    End With
  
    'Saves the new Excel file
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:=Path & Filename & ".xlsx", FileFormat:=xlOpenXMLWorkbook
    Application.DisplayAlerts = True
  
    'Closes the newly created workbook so you are still looking at the original workbook
    ActiveWorkbook.Close
  
End Sub

Note that DisplayAlerts is set to False so that you won't get a dialog box asking whether to save in a macro-free workbook. However, if a workbook with the same name already exists, it will be over-written.

Hope this helps!

 

[shahzeb123]

This code is working fine Thanks.

However, there is little problem with it. In my previous code when i save same name workbook multiple time it shows a dialog box for the duplication which i would like to have. but it is not showing me dialog box with this code

Moreover, this code does not delete the Form control button. but it does delete the pivot smoothly

Sorry from my side for not clearly defining the query. I have form control button and activex button as well.

 

[shahzeb123]

Sorry for responding to this thread without going through all codes.

Thank you very much!

You have explained very clearly about all codes and i have worked the code as per my needs.

here it is just for your review if there is some mistake in it.

Sub Button11_Click()

  
    Dim Path As String
    Path = "C:\Users\shakeelshahzeb\Desktop\Discounting\"
  
    'Gets the name of the currently visible worksheet
    Dim Filename As String
    Filename = Range("D5") & "-" & Format(Date, "dd-mmm-yy")
  
    'Copys the visible worksheet to a new workbook
    ThisWorkbook.ActiveSheet.Copy
  
    'Delete ActiveX command buttons
    Dim currentOleObject As OLEObject
    For Each currentOleObject In ActiveSheet.OLEObjects
        If TypeName(currentOleObject.Object) = "CommandButton" Then
            currentOleObject.Delete
        End If
    Next currentOleObject
  
   ActiveSheet.Buttons.Delete
  
    'Delete pivot tables
    Dim currentPivotTable As PivotTable
    For Each currentPivotTable In ActiveSheet.PivotTables
        currentPivotTable.TableRange2.Clear
    Next currentPivotTable

    With ActiveSheet.UsedRange
        .Value = .Value
    End With
  
    'Saves the new Excel file
    Application.DisplayAlerts = True
    ActiveWorkbook.SaveAs Filename:=Path & Filename & ".xlsx", FileFormat:=xlOpenXMLWorkbook
    Application.DisplayAlerts = True
  
    'Closes the newly created workbook so you are still looking at the original workbook
    ActiveWorkbook.Close
  
End Sub

 

[Domenic]

That's great, glad I could help!

Cheers!