Export a range from a Excel spreadsheet to a new file

export.vb

Sub ExportData(rng, fileFormat as Integer = 51)
    Dim irow As Integer
    Dim rngArr() as String
    
    fileSaveName = Application.GetSaveAsFilename(fileFilter:="xlsx Files (*.xlsx), *.xlsx")
    
    If fileSaveName <> False Then
        ActiveSheet.range(rng).Copy
        Set NewBook = Workbooks.Add
        Set rngArr() = Split(rng, ":")
        NewBook.Worksheets("Sheet1").range(rngArr(0)).PasteSpecial

        ' Available file formats (source: https://blogs.office.com/2009/07/07/use-the-vba-saveas-method-in-excel-2007/):
        ' 51 = xlOpenXMLWorkbook (without macro’s in 2007, .xlsx)
        ' 52 = xlOpenXMLWorkbookMacroEnabled (with or without macro’s in 2007, .xlsm)
        ' 50 = xlExcel12 (Excel Binary Workbook in 2007 with or without macro’s, .xlsb)
        ' 56 = xlExcel8 (97-2003 format in Excel 2007, .xls)
        
        NewBook.SaveAs fileSaveName, FileFormat:=fileFormat
    End If

End Sub