VBA Paste Data to New Worksheet If Row Number Exceeds 101

[Captain_Conman]

I currently have a worksheet with 277 rows of data. (One header and 276 transactions). I would like to limit each worksheet to 101 rows of data. (Header and 100 transactions). For example, instead of having 277 rows in Sheet 1, I would like Sheet 1 to have 101 rows (header and 100 transactions), Sheet 2 to have 101 rows (header and 100 transactions), and sheet 3 to have 77 rows (header and remaining 76 transactions). Ideally, this will work and make sheets regardless of how many transactions there are. For example, it would work if there are 276 or 884 etc.

Finally, I would like the sheet to be named based on the number of rows. In this example...
Sheet 1 = 101
Sheet 2 = 101
Sheet 3 = 77

Thank you in advance for help, I hope I laid my problem out okay.

 

[idlewyld89]

Sounds like a job for a nested loop!

You'll need 2 counter variables, one to count the total number of rows copied and one to count to 100.

Create separate subs for the following:
- Copy headers
- Copy rows
- Create new sheet

Use a Do Until loop to increment each copied row onto a separate sheet. Define your limit as the last row within your source sheet. Within THAT loop, you could use a Do While loop to increment up to your desired dataset length (100) before calling your "Create new sheet" routine to create a new sheet and continue the loop onto the next sheet.

 

[NoSparks]

You can't have more than one sheet with the same name.
Maybe something along the lines of this and then delete the original sheet

Sub DataToSheets()
    Dim src As Worksheet
    Dim lr As Long, lc As Long, x As Long
    Dim HeadArray As Variant, DataArray As Variant
    
Set src = ThisWorkbook.Sheets("Data")     '<~~~ alter sheet name as required
Application.ScreenUpdating = False
With src
    lr = .Cells(Rows.Count, 1).End(xlUp).Row
    lc = .Cells(1, Columns.Count).End(xlToLeft).Column
    HeadArray = .Range("A1").Resize(, lc).Value
    For x = 2 To lr Step 100
        DataArray = .Range("A" & x).Resize(100, lc).Value
        Sheets.Add After:=Worksheets(Worksheets.Count)
        ActiveSheet.Cells(1, 1).Resize(, lc) = HeadArray
        ActiveSheet.Cells(2, 1).Resize(100, lc) = DataArray
        ActiveSheet.Name = ActiveSheet.Name & " (" & ActiveSheet.UsedRange.Rows.Count - 1 & " Transactions)"
    Next x
End With
Application.ScreenUpdating = True
End Sub