Hi,

I am using a Citrix server environment in which I have Excel 2010 with the file format upon opening a new workbook as xls. The fact that is a Citrix server environment it won't retain the settings choice, therefore I cannot change the default format manually once.

I am trying to save a sheet from an xlsm to an xlsx, but due to the situation above I get an 1004 error. I tried to open the workbook and save it before copy/pasting the sheet, but the error happens on the marked line. I tried with both ".xlsx" and file format, without them, only with one at a time but same error pops up every time.

Anyone has any idea on how to fix it?
Thank you!

Sub sb_Copy_Save_Worksheet_As_Workbook()
     Application.ScreenUpdating = False
     Application.DisplayAlerts = False
     
     Dim wb As Workbook, WS As Worksheet
     Dim name As String
    
    Set wb = Workbooks.Add
    name = ThisWorkbook.Sheets("MO").Range("B4").Value
    wb.SaveAs ThisWorkbook.path & "\" & name & ".xlsx", FileFormat:=51
    ThisWorkbook.Sheets("Data").Copy After:=wb.Sheets(Worksheets.Count)
    
    For Each WS In wb.Worksheets
    If WS.name <> ActiveSheet.name Then WS.Visible = xlSheetHidden
Next WS

   wb.Save

    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub