Huge file - need to split it into a signle file with tabs based on the value of the rows

[SandsB]

Imagine a file with about 20,000 rows and each row has product data. The first column has the product name sorted so all the rows for a particular product are in contiguous rows. I need something to split up all the rows for each product into their own tabs - in this file or a new one. It doesn't matter how but I need one file with tabs for each product.

 

[Fluff]

How about

Sub SandsB()
   Dim Ary As Variant
   Dim i As Long
   Dim Ws As Worksheet
   
   Application.ScreenUpdating = False
   Set Ws = ActiveSheet
   Ary = Range("A2", Range("A" & Rows.Count).End(xlUp))
   With CreateObject("scripting.dictionary")
      .CompareMode = 1
      For i = 1 To UBound(Ary)
         .Item(Ary(i, 1)) = Empty
      Next i
      For i = 0 To .Count - 1
         Ws.Range("A1").AutoFilter 1, .Keys()(i)
         Worksheets.Add(, Sheets(Sheets.Count)).Name = .Keys()(i)
         Ws.AutoFilter.Range.EntireRow.Copy Range("A1")
      Next i
      Ws.AutoFilterMode = False
   End With
End Sub

 

[SandsB]

Brilliant. Thank you, Mr. Wizard.

 

[Fluff]

You're welcome & thanks for the feedback