PivotTable - Loop Through Choices, Refresh, Print to PDF

poleary2000

Active Member
Joined
Apr 1, 2002
Messages
354
I have a PivotTable that has one filter in the header. That filter has 1000 choices. I would like to sequentially select one of those at a time, refresh the PivotTable and save the active sheet to a PDF using Excel 2007 - continue to the next until finished with the list. I am decent with VBA, but have no idea where to start with a PivotTable VBA. Thoughts?
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
Hi, Patrick.

I understand the field is a page field. If not, it could be.

As such, what about using the 'show pages' option - which creates a worksheet for each choice in the field. Suggest you copy the workbook first.

I don't know about Excel 2007 but in previous versions there is a 'show pages' choice from the pivot table toolbar. So, set up the field as a page field and then use the show pages function. This should create lots of worksheets. Now just loop through and print each one.

OK?
 

poleary2000

Active Member
Joined
Apr 1, 2002
Messages
354
When I look at the PivotTable, the field I want to change is a Report Filter. Not sure if that is a Page Field or not???

I hadn't heard of that before. Will check it out now. The only issue I might have is that there would be 1000 sheets. Not sure if my compy can handle that!
 

poleary2000

Active Member
Joined
Apr 1, 2002
Messages
354
That function does seem to create the pages, but I'd rather loop through it without creating all the sheets. Good suggestion though. I have to pass the file off to someone else and I'd rather them just click a button and save to a file as opposed to creating all of those worksheets. Make sense?
 

poleary2000

Active Member
Joined
Apr 1, 2002
Messages
354

ADVERTISEMENT

I found this bit of code that gets me most of the way there. I just need to figure out how to save the file to a PDF and save the name based on a certain cell. Ideas?

Code:
Sub PrintPivotPages()
'prints a copy of pivot table for
'each item in page field
'assumes one page field exists
On Error Resume Next
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Set pt = ActiveSheet.PivotTables.Item(1)
For Each pf In pt.PageFields
For Each pi In pf.PivotItems
pt.PivotFields(pf.Name).CurrentPage = pi.Name
' ActiveSheet.PrintOut 'use this for printing
ActiveSheet.PrintPreview 'use this for testing
Next
Next pf
End Sub
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
Makes sense. Also makes sense to use the built-in functionality. :) So, suggest making a copy of the file then doing as above. Delete the copy of the file when done.

BTW, per your preferred method, I don't use Excel 2007 so can't code that.

cheers
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368

ADVERTISEMENT

Regarding the printing, suggest you use the macro recorder.

In particular, start the macro recorder, select a page field option, print to pdf, save the file where you want it, do one more, stop the recorder.

From the code generated, copy the lines you need to insert in the code already posted. The file name might be something like

pi.name & ".pdf"

HTH
 

poleary2000

Active Member
Joined
Apr 1, 2002
Messages
354
I used this:

Code:
Sub PrintPivotPages()
'prints a copy of pivot table for
'each item in page field
'assumes one page field exists
Application.ScreenUpdating = False

DirectoryLocation = ActiveWorkbook.Path

On Error Resume Next
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Set pt = ActiveSheet.PivotTables.Item(1)
For Each pf In pt.PageFields
For Each pi In pf.PivotItems
        pt.PivotFields(pf.Name).CurrentPage = pi.Name
        
        Columns("B:B").ColumnWidth = 8.14

        Name = DirectoryLocation & "\" & Range("B2").Value & ".pdf"
        
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Name _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=False
        
Next
Next pf

Application.ScreenUpdating = True

End Sub
 

Joven76

New Member
Joined
Oct 10, 2017
Messages
7
I used the first bit of code above to print a file for each filtered item in my pivot table. It has worked seamlessly up until just now. Now all it prints in the first item in the pivot table over and over and over again. It does not filter to the next item and print it, so I'm caught in a fatal loop. What's wrong?

Code:
Sub PrintPivotPages()'prints a copy of pivot table for
'each item in page field
'assumes one page field exists
On Error Resume Next
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Set pt = ActiveSheet.PivotTables.Item(1)
For Each pf In pt.PageFields
For Each pi In pf.PivotItems
pt.PivotFields(pf.Name).CurrentPage = pi.Name
' ActiveSheet.PrintOut 'use this for printing
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
Next
Next pf
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,130,330
Messages
5,641,547
Members
417,220
Latest member
lam150498

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top