VBA - copy file as macro free and keep the original open

[galvay]

I've seen some posts on how to save a file as macro-free workbook. But all of them result in currently open file becoming the new one.
What I need:
1. run the macro in currently opened xlsm file
2. save entire workbook (multiple sheets) as xlsx (macro-free), without opening xlsx - I don't want to see the new file, it's stored elsewhere as a 'result'.
3. continue to work in xlsm where user will run another macro later on.
Additional requirements:
- formulas must stay, so copying and pasting as values is not an option
- formulas can't reference to xlsm, so standard copying of worksheets is not an option either, as it keeps the external link/reference instead of internal one

 

[dmt32]

Hi,
have a look here:Saving a copy of the current workbbook as template

#post 3 provides a solution similar to your requirement & may do what you want

Dave

 

[Alex Blakenburg]

See if this does what you need:
Save As Keeping Original Open
It covers changing the Copy to the xlsx format removing the macros.

 

[galvay]

Thanks all, both tips gave me ultimately below code. Not the pretties one, but does the job I skipped the 'closing' part in the code on purpose for now, so both workbooks are open.

Dim original As Workbook
Dim newmacro As Workbook
Dim qname As String
Set original = ThisWorkbook
qname = Format(Date, "ddmmmyyyy")
NewFN = "C:\temp\DeleteMe.xlsm"
NewFNx = "C:\temp\" & qname & ".xlsx"
original.SaveCopyAs FileName:=NewFN
Set newmacro = Workbooks.Open(NewFN)
Application.DisplayAlerts = False
newmacro.SaveAs NewFNx, FileFormat:=xlOpenXMLWorkbook
Application.DisplayAlerts = True
Kill (NewFN)

 

[Alex Blakenburg]

Thanks for letting us know and for posting what you ended up using. Glad we could help.