Copy n' paste 150 rows on individual sheet with header then loop to next 150

[mahjohn]

The title pretty much says what I'm looking for, I need to breakout a sheet of 15,000+ rows into individual sheets of 150 data rows + header row from sheet 1. I just don't know enough coding to be able to make this happen. Appreciate the help, thanks.

 

[JLGWhiz]

Sub t()
Dim sh As Worksheet
Set sh = ActiveSheet
With sh
    For i = 2 To .UsedRange.Rows.Count - 1 Step 150
        Sheets.Add After:=Sheets(Sheets.Count)
        sh.Rows(1).Copy ActiveSheet.Range("A1")
        sh.Cells(i, 1).Resize(150).EntireRow.Copy ActiveSheet.Range("A2")
    Next
End With
End Sub

 

[Fluff]

How about

Sub mahjohn()
   Dim i As Long
   Dim Hdr As Variant
   
   Application.ScreenUpdating = False
   With ActiveSheet.UsedRange
      Hdr = .Rows(1).Value
      For i = 2 To .Rows.Count Step 150
         Sheets.Add(, Sheets(Sheets.Count)).Name = "Pass " & Application.RoundUp(i / 150, 0)
         Range("A1").Resize(, UBound(Hdr, 2)).Value = Hdr
         .Rows(i).Resize(150).Copy Range("A2")
      Next i
   End With
End Sub

 

[mahjohn]

These are awesome, thanks....

 

[Fluff]

You're welcome & thanks for the feedback

 

[mahjohn]

I tested both macros and they both work, but as usual my problem is more complicated than I thought and i'm struggling to explain what i'm trying to do, even though I do it manually..

Column A has a list of "Product Names"
Column B has a list of "IDs"

(neither of which are unique, there are duplicates, as a unique identifier is Column A+B+F+G+H)

I split the initial sheet into 150 row chunks which are submitted to create tickets for techs to work on. Tickets are created based on a combination of A+B:
If the Name of A2 = "XYZ" and B2 = "38169" and A3 = "XYZ" and B3 = "38169", etc.. One ticket is created for this.

However, as soon as there is a change in either the Product Name or the ID, a new ticket is generated. For example

As in the example above, A2 = "XYZ" and B2 = "38169" and A3 = "XYZ" and B3 = "38169" would equal one ticket, then A4 = "XYZ" + B4 = "JKD" would be the start of a new ticket, and A5 = "ABC" + A5 = "XYZ" would also be a new ticket as even though the ID is the same, the product has changed.

To further complicate things, if an A+B combination were to span two sheets, i.e., Row 150 on Sheet 1 and Row 2 on Sheet 2, this would result in two tickets on the same day for the same combination which cannot happen. I look at the sheet down to 152 (including header) and if the A+B combination in that row is the same as 151 i check 150, then 149, 148, etc...until get to where it changes...say...140, then that's my cut off. Up to row 140 is copied to a new sheet and the counting starts from 141 on a new sheet. If there is a time when there are greater than 150 of the same A+B combination, then the first 150 are on one sheet and the rest continue on another sheet by themselves with no other A+B combination, to be submitted another day....so complicated

 

[JLGWhiz]

According to formum posting guidelines, this would be a new topic and should be started in a new thread. It appears that what you are trying to manage is a continuation of the same selection criteriea between consecutive worksheets. You might want to consider that for your new thread topic.
Regards, JLG