Hi,
I have a master file with multiple rows.
I'm using VBA to split the master file into individual files each holding data from a single row (iRow).
When there are no more values in column A (NB: this does not mean that cells are blank as they contain formulas which do not return a TRUE value) the code should exit.
The individuals files are named after their respective cell (row = iRow, column = A)
I'm using the macro below however: it creates all individual files (i.e from irow to lastrow) but instead of stopping when it reaches lastrow it gives me an error message saying SaveAs method of Workbook class failed - so in essence it tries to create another file even though it has reached the end of data in that column.

What am i doing wrong? (I'm not very experienced in VBA so apologies if my mistake is blatantly obvious!)

Many Thanks
Roi

Sub splitfile()

Dim curWks As Worksheet
Dim newWks As Worksheet

Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long

Set curWks = Worksheets("Return Format")
Set newWks = Workbooks.Add(1).Worksheets(1)

With curWks
FirstRow = 2 'headers in row 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

.Rows(1).Copy
With newWks.Range("A1")
.PasteSpecial Paste:=xlPasteValues
.PasteSpecial Paste:=xlPasteFormats
End With

For iRow = FirstRow To LastRow
.Rows(iRow).Copy

With newWks.Range("A2")
.PasteSpecial Paste:=xlPasteValues
.PasteSpecial Paste:=xlPasteFormats
End With

newWks.Parent.SaveAs _
Filename:=ThisWorkbook.Path & "\" & .Cells(iRow, "A").Value & ".xlsm", _
FileFormat:=xlOpenXMLWorkbookMacroEnabled
Next iRow

End With

newWks.Parent.Close savechanges:=False

End Sub