Solved

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

Posted on 2014-12-02
3
113 Views
Last Modified: 2014-12-03
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
Comment
Question by:thutchinson
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 5

Expert Comment

by:Hakan Yılmaz
ID: 40477846
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
 
LVL 6

Accepted Solution

by:
Let's Go earned 500 total points
ID: 40477854
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
 

Author Closing Comment

by:thutchinson
ID: 40478537
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

628 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question