Solved

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

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

932 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

18 Experts available now in Live!

Get 1:1 Help Now