CODEWITHSUNDEEP
excelvba
Mark
Mark

Reputation: 71

Saving Worksheet as New Workbook Using Cell Value as Title

I have the following code:

Sub SaveFinalMTO()

Application.ScreenUpdating = False
Sheets("Final MTO").Select
Sheets("Final MTO").Copy

'grab the file name from b6:m6, put it in variable ThisFile
ThisFile = Sheets("Final MTO").Range("b6:m6").Value

Sheets("Final MTO").SaveAs Filename:="C:\Users\owner\Desktop\" & ThisFile & ".xlsm"

Application.ScreenUpdating = True
ActiveWorkbook.Close

End Sub

Everything works fine to an extent. The new workbook is created with the correct sheet. The new file is opened but the file name is "Book1" instead of the values in range B6:M6 which is a merged cell using a concatenate function. I tried using an unmerged with just a value, I was still pulling up a

run time error "13"

Any help solving this error would be much appreciated. Thank you.

Upvotes: 1

Views: 3442

Answers (1)

braX
braX

Reputation: 11755

If you are going to save as a macro enabled file type, then you have to specify that. Also, you can make the folder name dynamic using Environ$.

Sheets("Final MTO").SaveAs Filename:="C:\Users\" & Environ$("Username") & "\Desktop\" & ThisFile & ".xlsm", xlOpenXMLWorkbookMacroEnabled

XlFileFormat Enumerations

Upvotes: 1

Related Questions