Need code to delete excel tabs & self macro itself

[slr007]

Hi All,

I have been trying to code for the below scenario for over 2 months but no luck

I have an excel which is doing some calculations based on some code written on macro named "Calcu".

Once all the calculations are completed, I want to do below actions:-
1) Delete tabs "Sheet1" & "Sheet2". (Pop up thrown saying, tabs contain data, do you want to delete)
2) Delete the Macro named "Calcu".
3) Save the excel as a Non-Macro enabled, .xlsx but not .xlsm. (Pop up thrown saying, Are you sure you want to save as Non-Macro enabled)

Though I am able to partially achieve point 1 & 3 but it’s throwing above popups. And I want all popups to be taken care by macro itself, without any human intervention.
For point 2, I have gone through many other posts. All those talks about macro deleting other macros, but I want to delete the "Calcu" macro itself without introducing a new macro to excel as my ultimate aim is point 3.

Kindly help me with above issue.

 

[stumac]

Deleting the tabs without a prompt is easy, just add in application.displayalerts = false:

Sub test()
Application.DisplayAlerts = False
    With ActiveWorkbook
        .Sheets("Sheet1").Delete
        .Sheets("Sheet2").Delete
    End With
Application.DisplayAlerts = True
End Sub

Deleting macros is possible, but lengthy and AFIAK can't delete itself, also trying to get Excel to save a workbook with macros as an XLSX seems to be fairly difficult. There are a couple of 'better'/easier options...

1. Export the sheets you do want to keep to another workbook and save that as an XLSX OR
2. Run the macro from another workbook - Maybe in the Personal.XLSB