Split CSV Worksheet into Multiple CSV Files

Hi.
Ive been looking through the various posts on the forums and have tried a few bit of code which are almost doing what I need but my VB is pants to be honest, so I'd like some help if that's possible.

I found this post from yesteryear http://www.excelforum.com/excel-gene...s-by-rows.html which is pretty much what I need HOWEVER, the code here uses the first row as the header row (same as me) but then only takes the 2nd row into the new files. It works perfectly for that, but I need to extend the number of rows.

So rather than it being the header and one row, I need the header and 2000 rows, saving to each new workbook. The workbook I have, has just over 87,000 rows.

I'm sure the code on the above thread can be adapted by someone smarter than me, I just can figure it out I'm afraid.

Thanks for reading. Look forward to hearing any answers.

Chris

This should do.
Change fn to actual file path of Original csv.

Sub test()
    Dim rng As Range, i As Long, ws As Worksheet, n As Long
    Const fn As String = "c:\z-test1\samplewkbk.csv"  '<- alter to suite
    Set ws = ThisWorkbook.Sheets(1)
    Set rng = Workbooks.Open(fn).Sheets(1).Cells(1).CurrentRegion
    For i = 2 To rng.Rows.Count Step 2000
        ws.Cells.Clear
        Union(rng.Rows(1), rng.Rows(i).Resize(2000)).Copy ws.Cells(1)
        ws.Copy: n = n + 1
        With ActiveWorkbook
            .SaveAs Replace(fn, ".csv", "_" & Format$(n, "000") & ".csv"), xlCSV
            .Close False
        End With
    Next
    rng.Parent.Parent.Close False
    Sheets(1).Cells.Clear
End Sub

Thanks for the reply Jindon, the code works great but is there away to also copy the header (row 1) from the original worksheet into all the other sheets at the same time? Similar to the other thread code "FirstRow = 2 'headers in row 1" kind of thing. Thanks again

It should add the header to each newly created csv if you have header in the first row in the original csv.

And it is working here as it should be....

Sorry Jindon. My bad. yes, it works perfectly. Thanks very much for your time

That's OK and glad it is working.