Dynamic Printing help

[mle82]

Hi All

Desperately looking for some help on how to dynamically print a set of data over separate pages and can't seem to find any answers so hoping someone can help. I've got a list of customer orders with a unique product on each row with its associated order number. An order may have 1 or multiple products. I need it to print so that each order is on a separate page (with all the rows from that order) with the column headers. Example data below:

So from the above, I've be looking for 3 pages with page 1 having rows 2-4, page 2 having 3-7 and page 3 having 8-9 + row 1 each time. Thanks!

 

[Logit]

Option Explicit

Sub Create_separate_worksheet()
    Dim c As Range, sh As Worksheet, Ky As Variant
    
    Set sh = Sheets(1)
    With CreateObject("scripting.dictionary")
        For Each c In sh.Range("A2", sh.Range("A" & Rows.Count).End(xlUp))
            If c.Value <> "" Then .Item(c.Value) = Empty
        Next c
        For Each Ky In .Keys
            sh.Range("A1").AutoFilter 1, Ky
            Sheets.Add(, Sheets(Sheets.Count)).Name = Ky
            sh.AutoFilter.Range.EntireRow.Copy Range("A1")
        Next Ky
    End With
    sh.ShowAllData
End Sub

 

[mle82]

Thank you - this works perfectly!

 

[Logit]

You are welcome.