wont save as xlsm

orsm6

Active Member
Joined
Oct 3, 2012
Messages
399
Office Version
  1. 365
Platform
  1. Windows
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

Code:
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
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,898
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Does this do it .....UNTESTED
VBA Code:
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
 
Solution

orsm6

Active Member
Joined
Oct 3, 2012
Messages
399
Office Version
  1. 365
Platform
  1. Windows
Does this do it .....UNTESTED
VBA Code:
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

Well-known Member
Joined
Oct 27, 2005
Messages
20,898
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Glad to help... (y) :cool:
 

Forum statistics

Threads
1,141,402
Messages
5,706,224
Members
421,433
Latest member
yash0468

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