Is a macro ok?
Option Explicit
Sub testme()
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim CurWks As Worksheet
Dim TempWks As Worksheet
Dim myPath As String
Dim myFileName As String
Dim fCtr As Long
Application.ScreenUpdating = False
Set CurWks = Worksheets("Sheet1")
Set TempWks = Workbooks.Add(1).Worksheets(1)
'this folder has to exist!
myPath = "C:\my documents\excel\test"
If Right(myPath, 1) <> 1 Then
myPath = myPath & "\"
End If
With CurWks
FirstRow = 2 'headers in row 1???
'I used column A to determin the last row to process
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
fCtr = 0
For iRow = FirstRow To LastRow
Application.StatusBar = "Processing: " & iRow
fCtr = fCtr + 1
myFileName = Format(fCtr, "00000") & ".csv"
.Rows(iRow).Copy
TempWks.Range("a1").PasteSpecial Paste:=xlPasteValues
Application.DisplayAlerts = False
TempWks.SaveAs Filename:=myPath & myFileName, FileFormat:=xlCSV
Application.DisplayAlerts = True
TempWks.Cells.Clear 'probably not necessary, but it won't hurt
Next iRow
End With
TempWks.Parent.Close savechanges:=False
With Application
.StatusBar = False
.ScreenUpdating = True
End With
End Sub
