issue with splitting a large excel file into files of 100 rows. Also keeping leading Zero in zip code column

[spudn56]

issue with splitting a large excel file into files of 100 rows per file. I have attached a picture of the file I had tried an app but it also removes the leading zero in the zip code field even though I formated the cell named zip code with special-zip. I didn't know how to attach the file here sorry.

 

[JLGWhiz]

You might be able to work out something from this. It works OK on my PC with xl2013. New files are named according to first row of the group.

Sub t()
Dim i As Long
    With ActiveSheet
       For i = 1 To .Cells(Rows.Count, 1).End(xlUp).Row Step 100
            Workbooks.Add
            .Rows(i).Resize(100).Copy
            ActiveWorkbook.Sheets(1).Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
            ActiveWorkbook.SaveAs "Split " & i & ".xlsx", FileFormat:=51
            ActiveWorkbook.Close False
        Next
    End With
End Sub

 

[spudn56]

You might be able to work out something from this. It works OK on my PC with xl2013. New files are named according to first row of the group.

Sub t()
Dim i As Long
    With ActiveSheet
       For i = 1 To .Cells(Rows.Count, 1).End(xlUp).Row Step 100
            Workbooks.Add
            .Rows(i).Resize(100).Copy
            ActiveWorkbook.Sheets(1).Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
            ActiveWorkbook.SaveAs "Split " & i & ".xlsx", FileFormat:=51
            ActiveWorkbook.Close False
        Next
    End With
End Sub

Worked GREAT THANK YOU SO MUCH!

 

[JLGWhiz]

You're welcome,
Regards, JLG