PivotTable - Loop Through Choices, Refresh, Print to PDF

[poleary2000]

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?

 

[Fazza]

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]

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]

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]

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?

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]

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]

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]

I used this:

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]

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?

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

 

[Joven76]

Sorry, I mean to say print a page, not file.