Save As Copy with xlxs extension

LeonardSterk

New Member
Joined
Mar 21, 2021
Messages
33
Platform
  1. Windows
Good day,

After browsing many hours on the web and this forums that were very similar, I couldn't get my macro to work nor did I understand what I was doing anymore, hence why I am here to ask for help now...

VBA Code:
Sub Contracts()

    ThisWorkbook.Sheets("VGO").Copy                                                                                                                                      `Copy VGO Sheet
    ActiveSheet.Name = "Price"                                                                                                                                                  `Rename VGO sheet to Price
    ActiveWorkbook.SaveAs filename:="G:\Pro\Test\" & Worksheets("Price").Range("Q1") & ".xlsx"                                        `Save file with name that's at sheet Price, cell Q1 and save as xlsx
    ActiveWorkbook.Close                                                                                                                                                          `Close the new copy, I don't want to see it
    MsgBox "The file has been saved at G:\Pro\Test\" & Worksheets("Price").Range("F1"), vbInformation, "VGO"                  `MsgBox that it's saved 

End Sub

This works all fine, no errors. Except I don't think I did the extension change wrong, even though it says "xlsx"
Originally, the file is xlsm. When saving as a new copy I want it to be xlsx, yes I know macros won't work then, I want that.
Except, macros DO work. So I think it's still a xslm file but displaying as xlsx.

Long question short:
- Do everything above but make it copy to .xlsx
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,727
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
You should always specify the FileFormat argument when performing a SaveAs operation.
 

LeonardSterk

New Member
Joined
Mar 21, 2021
Messages
33
Platform
  1. Windows
I tried removing & ".xlsx" and replacing with FileFormat:=51
But that didn't work

P.S.
I meant to say I do think I did the extension change wrong (I said don't, oops)
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,727
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Code:
ActiveWorkbook.SaveAs filename:="G:\Pro\Test\" & Worksheets("Price").Range("Q1") & ".xlsx", fileformat:=51

will save the workbook in macro free format.
 
Solution

LeonardSterk

New Member
Joined
Mar 21, 2021
Messages
33
Platform
  1. Windows

ADVERTISEMENT

Code:
ActiveWorkbook.SaveAs filename:="G:\Pro\Test\" & Worksheets("Price").Range("Q1") & ".xlsx", fileformat:=51

will save the workbook in macro free format.
Hey thanks I just tried this
When opening the saved xlsx file I can still use Macros. And I thought that wasn't a thing in xlsx?
That's why I think it's still a xlsm file but idk
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,727
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
You can still use macros but the macros will not be in that workbook.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,989
Messages
5,639,405
Members
417,086
Latest member
bfruge

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