Excel VBA: save a macro-enabled Excel file as a non-macro file

 In this code, you need to replace "C:\Users\UserName\Documents\NewFile.xlsx" with the file path and name where you want to save the new file. The xlOpenXMLWorkbook file format argument specifies that the file should be saved as a non-macro-enabled file.

Before saving the file, the code disables macros using the EnableReferences property of the VBE project. After saving the file, it enables macros again. This is important because if the file is saved with macros enabled, it will still be a macro-enabled file even if the file extension is changed.

Sub SaveAsNonMacroFile()

    'Set the file path and name for the new file

    Dim NewFilePath As String

    NewFilePath = "C:\Users\UserName\Documents\NewFile.xlsx"

    

    'Disable macros

    ThisWorkbook.VBProject.VBE.ActiveVBProject.References.EnableReferences = False

    

    'Save the file as a non-macro-enabled file

    ActiveWorkbook.SaveAs Filename:=NewFilePath, FileFormat:=xlOpenXMLWorkbook

    

    'Enable macros

    ThisWorkbook.VBProject.VBE.ActiveVBProject.References.EnableReferences = True

End Sub