Save sheet without macro's

[Steve001]

Hello All,

I have a excel macro that is saved on a notepad text file to use i copy and paste this into the excel sheet in question.

When the code is run at the end of the macro i would like the sheet to save as a .xls without a warning.
i found a thread here, that works ok,

Macro to remove macro that removes macros

I have the below VBA code to remove all macros from a workbook, but of course this macro remains. Is there a way to remove all macros and also remove this macro after it is done, so there are no macros left at all? Sub remove_macros() Dim m As Object Dim mCtr As Integer Dim oCtr...

www.mrexcel.com

Private Sub savecopynomacros()

ThisWorkbook.Save ' save any unsaved changes
Application.DisplayAlerts = False
ThisWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & Replace(ThisWorkbook.Name, ".xlsm", "") & " no macros", FileFormat:=xlOpenXMLWorkbook
Application.DisplayAlerts = True
'ThisWorkbook.Close

End Sub

however this creates another file i would like to just save the file that is open with the same name.
I am not sure how to modify the code as it adds a bit to the file name and changes the file type.

when i delete this part of code

& " no macros", FileFormat:=xlOpenXMLWorkbook

i get Error 1004 "Application-defined or Object-defined error"


I am using office 363 & office 2021

Regards

Steve

 

[John_w]

Try this. Note the Kill line (commented out) deletes the original .xlsm file.

Public Sub Save_Workbook_As_xlsx()
       
    Dim p As Long, xlsm As String
    xlsm = ThisWorkbook.FullName
    p = InStrRev(xlsm, ".")
    Application.DisplayAlerts = False
    ThisWorkbook.SaveAs Left(xlsm, p) & "xlsx", xlOpenXMLWorkbook
    Application.DisplayAlerts = True
    Application.Quit
    'Kill xlsm
    ThisWorkbook.Close False
    
End Sub

 

[Steve001]

Hi John

Works a treat thank you

Steve