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

galvay

New Member
Joined
Jul 18, 2022
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

galvay

New Member
Joined
Jul 18, 2022
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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.
VBA Code:
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)
 
Solution

Alex Blakenburg

MrExcel MVP
Joined
Feb 23, 2021
Messages
4,990
Office Version
  1. 365
Platform
  1. Windows
Thanks for letting us know and for posting what you ended up using. Glad we could help.
 

Forum statistics

Threads
1,175,479
Messages
5,897,673
Members
434,668
Latest member
alteriusquiuus

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top