Steve001
Board Regular
- Joined
- Apr 13, 2017
- Messages
- 91
- Office Version
- 365
- 2021
- 2013
- Platform
- Windows
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,
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
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...
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