VBA/Macro to select specific worksheets and save them as separate files

[thechad]

Greetings,

I found this snippet of code in a Mr Excel post from 2018 that seems simple enough however I continue to receive the error below. Not sure what the issue is but would appreciate any assistance.

Sub SaveSheetsAsFiles()

Dim SheetsToSave
SheetsToSave = Array("407 & HALTON", "DURHAM", "HAMILTON") 'change tab names to suit

Application.ScreenUpdating = False

For Each sht In Sheets(SheetsToSave)
    sht.Copy
    'file location is same as workbook the code is in. Change to suit
    'file format is .xlsm - change to suit
    ActiveWorkbook.SaveAs Filename:=sht.Name & ".xlsx", FileFormat:=52

Next sht

Application.ScreenUpdating = True

End Sub

ERROR:
Run-time error '1004':

This extension cannot be used with the selected file type. Change the file extension in the File name text box or select a different file type by changing the Save as type.

It will generate one of the pages and then the error arises.

Thanks in advance!

 

[Trebor76]

Hi there,

For xlsx files the FileFormat is 51 not 52 (these are for xlsm files) as you have it.

Regards,

Robert

 

[thechad]

Yessssss......you are right! I vaguely remember something about that in days gone by. Thank you! As I have had some time to play with other stuff that I found that didn't do what I was looking for, I am noticing that it is copying everything, including the formulas. Is there a way to copy the values/formats and ditch the formulas in the new versions?

C

 

[thechad]

Robert...I was able to cobble together a solution but would've gone mad without your assistance. Thanks again!!!!!!!

 

[Trebor76]

Robert...I was able to cobble together a solution but would've gone mad without your assistance. Thanks again!!!!!!!

You're welcome Glad we got it sorted