Solved

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

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

Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

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

Suggested Solutions

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
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…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

831 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