Macro to split sheet into multiple CSV files with 1000 lines

[Jarryd Moore]

I'm trying to create a macro that will take the current sheet and split it into multiple CSV files, batched at 1000 lines each.

The below code is what I have. It's saving the files but each file contains the same data (row 2 to row 1001). I need each file to be a batch of the whole set of data (ie. row 2 to row 1001, row 1002 to row 2001, row 2002 to 3001, etc).

What have I got wrong?

Sub Split_1000_With_Column_Headings()

    Dim inputFile As String, inputWb As Workbook
    Dim lastRow As Long, row As Long, n As Long
    Dim newCSV As Workbook
    
    inputFile = Application.ThisWorkbook.FullName
    
    Set inputWb = Workbooks.Open(inputFile)
    
    With inputWb.ActiveSheet
        lastRow = .Cells(Rows.Count, "A").End(xlUp).row
        
        Set newCSV = Workbooks.Add
        
        n = 0
        For row = 2 To lastRow Step 1000
            n = n + 1
            .Rows(1).EntireRow.Copy newCSV.ActiveSheet.Range("A1")
            .Rows(row & ":" & row + 1000 - 1).EntireRow.Copy newCSV.ActiveSheet.Range("A2")
            
            'Save in same folder as input workbook with .xlsx replaced by (n).csv
            newCSV.SaveAs Filename:=inputWb.Path & "\Exports\" & "Contacts Import for Xero " & Format(Date, "yyyymmdd") & " (" & n & ").csv", FileFormat:=xlCSV, CreateBackup:=False
        Next
    End With
    
    newCSV.Close saveChanges:=False
    inputWb.Close saveChanges:=""
    
End Sub

 

[Joe4]

Welcome to the Board!

Most of your code works fine for me. The only problem I see is with this part here:

    inputFile = Application.ThisWorkbook.FullName
    
    Set inputWb = Workbooks.Open(inputFile)

It looks like you are setting the "inputFile" variable to be the full name of the current workbook (which would be the file with the macro).
Then, on the next line, you are telling it to open the file, but the file is already open! So I am not sure what you are hoping to accomplish with that piece.

Assuming that the data is in the same file as the macro, I changed the second row above to this, and it worked as expected.

    Set inputWb = ActiveWorkbook

If the data is in a separate file, and you want to prompt the user to browse to the data file, then use this instead:

    inputFile = Application.GetOpenFilename _
        (Title:="Please choose a file to open", _
        FileFilter:="Excel Files *.xls* (*.xls*),")
    
    Set inputWb = Workbooks.Open(inputFile)