wont save as xlsm

[orsm6]

Hi all - I found the below code on this forum and i can make it work if saving the new workbook as .xlsx... but I need to save it as .xlsm however when I try i get run-time error 1004.

how can i solve this? TIA

Sub CreateNewMaster()
 
    Dim FName           As String
    Dim FPath           As String
    Dim NewBook         As Workbook
 
    Application.ScreenUpdating = False
    FPath = Worksheets("main menu").Cells(5, 15).Value
    FName = Worksheets("main menu").Cells(6, 15).Value & ".xlsx"
 
    Set NewBook = Workbooks.Add
 
    ThisWorkbook.Sheets("Varun").Copy Before:=NewBook.Sheets(1)
 
    If Dir(FPath & "\" & FName) <> "" Then
        MsgBox "File " & FPath & "\" & FName & " already exists"
    Else
        NewBook.saveas Filename:=FPath & "\" & FName
        ActiveWorkbook.Close
    End If
 
End Sub

 

[Michael M]

Does this do it .....UNTESTED

Sub CreateNewMaster()
Dim FName As String, FPath As String, NewBook As Workbook
    Application.ScreenUpdating = False
    FPath = Worksheets("main menu").Cells(5, 15).Value
    FName = Worksheets("main menu").Cells(6, 15).Value
    Set NewBook = Workbooks.Add
    ThisWorkbook.Sheets("Varun").Copy Before:=NewBook.Sheets(1)
    If Dir(FPath & "\" & FName) <> "" Then
        MsgBox "File " & FPath & "\" & FName & " already exists"
    Else
        NewBook.SaveAs Filename:=FPath & "\" & FName, FileFormat:=52
        ActiveWorkbook.Close
    End If
End Sub

 

[orsm6]

Does this do it .....UNTESTED

Sub CreateNewMaster()
Dim FName As String, FPath As String, NewBook As Workbook
    Application.ScreenUpdating = False
    FPath = Worksheets("main menu").Cells(5, 15).Value
    FName = Worksheets("main menu").Cells(6, 15).Value
    Set NewBook = Workbooks.Add
    ThisWorkbook.Sheets("Varun").Copy Before:=NewBook.Sheets(1)
    If Dir(FPath & "\" & FName) <> "" Then
        MsgBox "File " & FPath & "\" & FName & " already exists"
    Else
        NewBook.SaveAs Filename:=FPath & "\" & FName, FileFormat:=52
        ActiveWorkbook.Close
    End If
End Sub

Hi Michael - it certainly did - and thank you

 

[Michael M]

Glad to help...