Solved

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

Posted on 2014-12-02
3
104 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
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

707 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now