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

shahzeb123

New Member
Joined
Jul 29, 2021
Messages
37
Office Version
  1. 2016
Platform
  1. Windows
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.

VBA Code:
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
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,927
Office Version
  1. 365
Platform
  1. Windows
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...

VBA Code:
    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...

VBA Code:
    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...

VBA Code:
    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...

VBA Code:
    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...

VBA Code:
    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......

VBA Code:
    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...

VBA Code:
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!
 
Solution

shahzeb123

New Member
Joined
Jul 29, 2021
Messages
37
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
Jul 29, 2021
Messages
37
Office Version
  1. 2016
Platform
  1. Windows
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.

VBA Code:
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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,927
Office Version
  1. 365
Platform
  1. Windows
That's great, glad I could help!

Cheers!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,958
Messages
5,767,321
Members
425,404
Latest member
Bairkus

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top