If you run the code in Excel 2007-2016 it will look at the File Format of the parent workbook and save the new file in that format. Cut Copy Mode = False Temp File Path = Application.
File Format Case 51: File Ext Str = ".xlsx": File Format Num = 51 Case 52: If . Close Save Changes:=False End With Msg Box "You can find the new file in " & Temp File Path With Application . Enable Events = True End With End Sub Sub Copy_Active Sheet_2() 'Give the user the choice to save in 2000-2003 format or in one of the 'new formats.Has VBProject Then File Ext Str = ".xlsm": File Format Num = 52 Else File Ext Str = ".xlsx": File Format Num = 51 End If Case 56: File Ext Str = ".xls": File Format Num = 56 Case Else: File Ext Str = ".xlsb": File Format Num = 50 End Select Dim File Ext Str As String Dim File Format Num As Long Dim Sourcewb As Workbook Dim Destwb As Workbook Dim Temp File Path As String Dim Temp File Name As String With Application . Enable Events = False End With Set Sourcewb = Active Workbook Select Case Sourcewb. Use the "Save as type" dropdown to make a choice, Default = 'Excel Macro Enabled Workbook.File Format Case 51: File Ext Str = ".xlsx": File Format Num = 51 Case 52: If . You can add or remove formats to/from the list fname = Application.You see a lot of old Save As code that does not specify the File Format parameter.In Excel versions before Excel 2007, code without this parameter will not cause too many problems because Excel will use the current File Format of the existing file and the default File Format for new files is a (xls) in 97-2003 because there are no other Excel file formats before Excel 2007.
But because there are so many new file formats in Excel 2007-2016, we shouldn't use code like this that does not specify the File Format parameter.In Excel 2007-2016, Save As requires you to provide both the File Format parameter and the correct file extension.For example, in Excel 2007-2016, this will fail if the Active Workbook is not an xlsm file' 52 = xl Open XMLWorkbook Macro Enabled = xlsm (with macro's in 2007-2016) These are the main file formats in Excel 2007-2016, Note: In Excel for the Mac the values are +151 = xl Open XMLWorkbook (without macro's in 2007-2013, xlsx)52 = xl Open XMLWorkbook Macro Enabled (with or without macro's in 2007-2013, xlsm) 50 = xl Excel12 (Excel Binary Workbook in 2007-2013 with or without macro's, xlsb)56 = xl Excel8 (97-2003 format in Excel 2007-2013, xls)Note: I always use the File Format numbers instead of the defined constants in my code so that it will compile OK when I copy the code into an Excel 97-2003 workbook (For example, Excel 97-2003 won't know what the xl Open XMLWorkbook Macro Enabled constant is).Below are two basic code examples to copy the Active Sheet to a new Workbook and save it in a format that matches the file extension of the parent workbook.The second example use Get Save As Filename to ask you for a file path/name.Example 1 you can use in Excel 97-2016 , Example 2 you can use in Excel 2000-2016.