asked on

How can I group records from a spreadsheet, add page breaks, and print sequentially?

We need to send a report to each member (our customers) listing their purchases and the rebate we owe them on those purchases. In other words, I need to prepare a separate printed report for each Member. Since we have hundreds of members I need to automate the printing of this report using VBA or some other method an Expert recommends. Hopefully, one command will print a report for each member sequentially. So, at each change in MemberID, I need to start a new page. See Pivot Data tab for field organization.

Thanks for your help!
Member-by-Supplier-at-each-rate.xlsx

Hakan Yılmaz

Here is my initiation of solution, anyone may build over.

Sub PrintOrSendSeperateReports()
    
    Dim ptable As PivotTable
    Set ptable = Sheet1.PivotTables(1)
    
    Dim pfield As PivotField
    Set pfield = ptable.PivotFields("MEMBERNAME")
   
    Dim pitems As PivotItems
    Set pitems = pfield.PivotItems

    Dim pitem As PivotItem
    For Each pitem In pitems
        Debug.Print pitem.Position & " " & pitem.Name & " " & pitem.DataRange.Address & " " & pitem.RecordCount
    Next pitem

End Sub

Open in new window

ASKER CERTIFIED SOLUTION

Let's Go

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

thutchinson

ASKER

Terrrific! Thanks for showing me such a simple solution. Amazing that I never saw that setting before. The output is plenty fancy enough after I make a few more changes to the settings and page setup. I just need to add some titles and I'm ready to go. Thanks again.