help: update pivot table and printing it

[MartinL]

Hi

I have 130 customers which I want to print out order forms for.
The way the spreadsheet is written is I am using a pivot table to select the customer which then populates nicly with some additional columns of lookup data, thhrown in for good measure.

The print area is set so i can select 1 at a time manually and it works great. My problem now, is that I need to print out a batch of these weekly, by pushing one button.

Can I get excel to loop through a

• change the "report filter"
• print worksheet

scenario until all the retailers orders have been printed

 

[MartinL]

OK

So I have found this useful piece of code at Printing PivotTables &  PivotCharts. Print Each Page Field (Report Filter) Of Pivot Tables & Pivot Charts

Sub Print_Batch()

Dim pt As PivotTable, pi As PivotItem, pf As PivotField
Dim lLoop As Long
 

Set pt = Sheet2.PivotTables("PTSuggOrd)
Set pf = Slicer_Store        'how do I do this?
 
For Each pi In pf.PivotItems
    pi.Value = pi.Value
    'Sheet1.PivotTables(1).PageFields(1).CurrentPage = pi.Value
    Sheet1.PrintOut
    lLoop = lLoop + 1
Next pi
End Sub

 

[MartinL]

I have decided for now to take my slicer and add it as a Report Filter

And it works in a fashion!
What I am finding now is that not all retailers make orders every week, typically 50% do
so the print function is currently printing out twice as many orders than is required
The slicer visually indicates items with no values, by greying them out.
Can I make use of this, so only orders with values are printed?
Martin

 

[MartinL]

simplistic way

change this

Sheet1.PrintOut

to

If Range("C17") <> "" Then Sheet2.PrintOut

where C17 is the first cell containing pivot data

so the whole macro looks like this

Sub Print_Batch()

Dim pt As PivotTable, pi As PivotItem, pf As PivotField
Dim lLoop As Long
 

Set pt = Sheet2.PivotTables("PTSuggOrd")
Set pf = pt.PageFields("Outlet")
 
For Each pi In pf.PivotItems
    Sheet2.PivotTables("PTSuggOrd").PageFields("Outlet").CurrentPage = pi.Value
    If Range("C17") <> "" Then Sheet2.PrintOut
    lLoop = lLoop + 1
Next pi
End Sub