split a large excel into smaller files but include the header in all files

Question

I have a macro that I use to break large excel spreadsheets into smaller files. It works perfectly, except, it only uses the header row in the first file created and this header row (row 1) needs to be at the top of each new file. Is there a way to modify this code to somehow insert that row into all the files?

Sub SplitSheets()

' Save sheet in rows of 25000 to incremental CSV files
' JBeaucaire (7/27/2009)

Dim LR As Long, i As Long, Cntr As Long

Dim ws As Worksheet, OldDir As String

If MsgBox("Is this the sheet to parse data from?", vbYesNo + vbQuestion) = vbNo Then Exit Sub LR = Range("A" & Rows.Count).End(xlUp).row

Set ws = ActiveSheet

OldDir = CurDir     'memorizes the user's current working path

Dim v: v = Evaluate("ISREF(TEMP!A1)")

    If Not v Then
        Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Temp"
    Else
        Sheets("Temp").Activate
        Cells.Clear
    End If

ChDir "C:\Users\BartB\Desktop\sheets"     'path to save CSV file into

    For i = 1 To LR Step 2000
        ws.Rows(i & ":" & i + 1999).Copy Range("A1")
        Cntr = Cntr + 1
        ActiveWorkbook.SaveAs Filename:="File" & Cntr & ".csv", FileFormat:=xlCSV, CreateBackup:=False
        Cells.Clear
    Next i

ChDir OldDir        'restores user's original working path
End Sub

Answer 1

You will have to create the step in code. Right before the For Next loop write the code hard wired from row one to row one of the new file. After that just make sure you start writing at line two. This is accomplished by changing the line "For i = 2 to LR Step 2000"

    ws.Rows("1:1").Copy Range("A1")
    Cntr = Cntr + 1
    ActiveWorkbook.SaveAs Filename:="File" & Cntr & ".csv", FileFormat:=xlCSV, CreateBackup:=False
    Cells.Clear

For i = 1 To LR Step 2000
    ws.Rows(i & ":" & i + 1999).Copy Range("A1")
    Cntr = Cntr + 1
    ActiveWorkbook.SaveAs Filename:="File" & Cntr & ".csv", FileFormat:=xlCSV, CreateBackup:=False
    Cells.Clear
Next i

Play with it but that is the idea.