Split a single spreadsheet in XLS into multiple new workbooks

[craigfer]

I have a software that i need to upload information. The software has a 500 line limit on the Excel file import.
I have 84,968 lines of data in a single workbook. Is there a way of splitting up this data into the 169 seperate workbooks (induvidual files not worksheets).
These new workbooks need to be named differently. I can add a Batch column in the original file and give the cell a name so the first 500 lines would be BAT001, the next 500 would be BAT002

 

[John_w]

Try this macro - no need for the Batch column.

Public Sub Split_Sheet_Into_New_Workbooks()

    Dim rangeToCopy As Range
    Dim wbCount  As Long
    Dim maxRows As Long
    Dim r As Long, c As Long
    Dim wb As Workbook

    maxRows = 500
    wbCount = 1
    
    Application.ScreenUpdating = False
        
    With ActiveWorkbook.ActiveSheet
        c = .Cells(1, .Columns.Count).End(xlToLeft).Column
        For r = 1 To .Cells(.Rows.Count, 1).End(xlUp).Row Step maxRows
            Set wb = Workbooks.Add
            Set rangeToCopy = .Cells(r, 1).Resize(maxRows, c)
            rangeToCopy.Copy wb.Worksheets(1).Range("A1")
            Application.DisplayAlerts = False
            wb.SaveAs ThisWorkbook.Path & "\BAT" & Format(wbCount, "000") & ".xlsx", FileFormat:=xlOpenXMLWorkbook
            Application.DisplayAlerts = True
            wb.Close
            wbCount = wbCount + 1
        Next
    End With
    
    Application.ScreenUpdating = True

End Sub