Looping through in Pivot table Filter fields

Hey guys,

I have a big problem and I hope that you can help me I want to print all of the filter fields (all of the offers) in a pivot table, and print the tables one below another to the same page. Iīm trying with the following code:

Dim PvtTbl1 As PivotTable
Dim pvtFld1 As PivotField
Dim pi1 As PivotItem
Dim wsData1 As Worksheet
Dim rngData1 As Range
Dim PvtTblCache1 As PivotCache
Dim wsPvtTbl1 As Worksheet


Set wsData1 = Worksheets("Uber")
Set wsPvtTbl1 = Worksheets("Uber_opt")
Set rngData1 = wsData1.Range("A1").CurrentRegion


ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rngData1, Version:=xlPivotTableVersion12).CreatePivotTable TableDestination:=wsPvtTbl1.Range("A1"), TableName:="Uber_pivot"
Set PvtTbl1 = wsPvtTbl1.PivotTables("Uber_pivot")



Set pvtFld1 = PvtTbl1.PivotFields("offer")
pvtFld1.Orientation = xlPageField

Set pvtFld1 = PvtTbl1.PivotFields("publisher")
pvtFld1.Orientation = xlRowField

Set pvtFld1 = PvtTbl1.PivotFields("conversions")
pvtFld1.Orientation = xlDataField


Set pvtFld1 = PvtTbl1.PivotFields("revenue")
pvtFld1.Orientation = xlDataField

With PvtTbl1.PivotFields("First Ride")
.Orientation = xlDataField
.Function = xlSum
.NumberFormat = "#,##0"
.Position = 1
End With

For Each pi1 In PvtTbl1.PageFields("offer").PivotItems
PvtTbl1.PageFields("offer").CurrentPage = pi1.Name
PvtTbl1.ManualUpdate = True

Range("A3").End(xlDown).Offset(3, 0).Select


Next pi1

The code rolls down to the last item in the Filter field and prints only that table instead of all of them. Can you help me, please?

Thank you,

Best,

Dani

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

For Each pi1 In PvtTbl1.PageFields("offer").PivotItems
PvtTbl1.PageFields("offer").CurrentPage = pi1.Name
PvtTbl1.ManualUpdate = True

Range("A3").End(xlDown).Offset(3, 0).Select
Next pi1

there is no printing (or extracting) being done here?
did you leave something out?

Hey humdingaling!

Thank you for your quick reply. This is the first time Iīm using VBA to create Pivot tables. When I do it without the loop, the
Select Code
PvtTbl1.ManualUpdate = True
prints the table, simply like this:

[/CODE]

PvtTbl1.ManualUpdate = False

Dim PvtTbl2 As PivotTable
Dim wsData2 As Worksheet
Dim rngData2 As Range
Dim PvtTblCache2 As PivotCache
Dim wsPvtTbl2 As Worksheet
Dim pvtFld2 As PivotField

Set wsData2 = Worksheets("Stubhub")
Set wsPvtTbl2 = Worksheets("Stubhub_opt")
Set rngData2 = wsData2.Range("A1").CurrentRegion

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rngData2, Version:=xlPivotTableVersion12).CreatePivotTable TableDestination:=wsPvtTbl2.Range("A1"), TableName:="Stubhub_pivot"
Set PvtTbl2 = wsPvtTbl2.PivotTables("Stubhub_pivot")

PvtTbl2.ManualUpdate = True

Set pvtFld2 = PvtTbl2.PivotFields("offer")
pvtFld2.Orientation = xlPageField

Set pvtFld2 = PvtTbl2.PivotFields("publisher")
pvtFld2.Orientation = xlRowField

Set pvtFld2 = PvtTbl2.PivotFields("conversions")
pvtFld2.Orientation = xlDataField

Set pvtFld2 = PvtTbl2.PivotFields("revenue")
pvtFld2.Orientation = xlDataField

With PvtTbl2.PivotFields("Purchase")
.Orientation = xlDataField
.Function = xlSum
.NumberFormat = "#,##0"
.Position = 1
End With


PvtTbl2.ManualUpdate = False

[/CODE]

Which command should I use to print the table?

Thank you guys,

Dani

you print tables the same way you print normal sheets in excel

heres a modified example of what you could do...printing to pdf in this case

For Each pi1 In PvtTbl1.PageFields("offer").PivotItems
PvtTbl1.PageFields("offer").CurrentPage = pi1.Name

        strFileName = ThisWorkbook.Path & "\print\" & pi1.Name
        With ActiveSheet
            .PageSetup.PrintArea = "B1:O" & Cells(Rows.Count, "B").End(xlUp).Row ' you need to update this to match your print area setup
            .ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFileName, IgnorePrintAreas:=False, OpenAfterPublish:=False
        End With
    Next

^
this prints makes pi into a pdf into the \print\ subfolder of ThisWorkBook.Path
if you want the headers to repeat..you look that into pagesetup prior to running the code
you can shrink to fit on one page also ..this would require extra code