Run Time Error 1004

[bloodmilksky]

Hi Guys,

I am using the below to save sheets as workbooks but it gives me a run time error 1004 saying this file cant be saved using the extension I have requested but my understanding is that .xlsm is the correct extension

Sub Export_Sheet_To_Order_File()
Dim wb As Workbook
Dim myPath As String
Dim myFilename As String
Dim myFileExtension As String
Dim Msg As String
Dim Ans As VbMsgBoxResult


    myPath = "\\EUCORP-FCL-CM01\cmdeptdata$\custsrv\CS"    'you can change this
    myFileExtension = ".xlsm"
    myFilename = ThisWorkbook.Sheets("EDIorder").Range("A2").Value
    ThisWorkbook.Sheets("EDIorder").Copy
    Set wb = ActiveWorkbook
    With wb
    .ActiveSheet.UsedRange.Value = .ActiveSheet.UsedRange.Value
    .SaveAs myPath & myFilename & myFileExtension
    .Close False
    End With


 Msg = "Would You Like To Load Another Order ? " & Application.UserName
    Ans = MsgBox(Msg, vbYesNo)
    If Ans = vbNo Then
                Range("BP_No").Select
                Selection.ClearContents
                Range("Customer_Order_Number").Select
                Selection.ClearContents
                Range("Description").Select
                Selection.ClearContents
                Application.DisplayAlerts = False
                ThisWorkbook.Save
                Application.DisplayAlerts = True
                Application.Quit
                End If
    
    If Ans = vbYes Then
                Sheets("EDIorder").Select
                Range("A1:X600").Select
                Selection.ClearContents
                Sheets("Order Sheet").Select
                Range("Description").Select
                Selection.ClearContents
                Range("PXINFOR").Select
                Selection.ClearContents
                Range("BP_No").Activate
                Range("BP_No").Select
                End If


End Sub

can anyone help? please

 

[JoeMo]

Assuming you have a valid file name in myFilename, you need a path separator and the specification of a file format. Try:

.SaveAs myPath & Application.Pathseparator & myFileName & myFileExtension, Fileformat:=52

 

[Norie]

Try also specifying the file format with the FileFormat argument.

    .SaveAs myPath & myFilename & myFileExtension, FileFormat:=xlOpenXMLWorkbookMacroEnabled