Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 115
  • Last Modified:

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
0
thutchinson
Asked:
thutchinson
1 Solution
 
Hakan YılmazTechnical Office MEP EngineerCommented:
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

0
 
Let's GoCommented:
When you say "report", do you just mean the rows for the relevant Member in the Pivot table?  If so, the solution is easy.  For your sample spreadsheet:

Right-click on GORM Inc. Total (i.e. the first MEMBERNAME Subtotal)
Select Field Settings
Click on the Layout and Print tab, then click Insert page break after each item, the click OK.
Then, print as normal.  Each Member will be printed on a separate page.

Alternatively, if you want a fancy report, you could consider a mail merge into Word.
0
 
thutchinsonAuthor Commented:
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.
0

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now