How to loop through PivotTable
 

I have a Pivot Table already created. However, I am programming a module and
I want to access the information contained in the pivot table and loop
through it to change the data and print a page for each item in the table. I
understand how to write a loop but I am having trouble locating how to access
the data in the pivot table.

How to loop through PivotTable
 

Here is an example of a loop that I did with a pivot table
With ActiveChart.PivotLayout.PivotTable.PivotFields("Fr equency")
For cnt = 1 To .PivotItems.Count
If .PivotItems(cnt).Value < minFreq Or .PivotItems(cnt)
maxFreq Then
.PivotItems(cnt).Visible = False
Else
cnt2 = cnt2 + 1
End If
Next
End With

Does that help any?


Die_Another_Day

"ShadowVixen" wrote in message
...
I have a Pivot Table already created. However, I am programming a module
and
I want to access the information contained in the pivot table and loop
through it to change the data and print a page for each item in the table.
I
understand how to write a loop but I am having trouble locating how to
access
the data in the pivot table.

How to loop through PivotTable
 

I am not sure - now I am getting an error message that says "Cant find
project or library". My code is below:

Sub PrintForms()
Dim StartRow
Dim EndRow
Dim Msg As String
Dim i

Sheets("1").Activate
With ActiveChart.PivotLayout.PivotTable.PivotFields("Mo nid")
For i = 1 To .PivotItems.Count
Range("B1") = PivotFields("Monid").Value
ActiveSheet.PrintOut
Next
End With


End Sub

How to loop through PivotTable
 

This is because you haven't selected the Pivot Chart. Is the chart on a
separate page?

Die_Another_Day
ShadowVixen wrote:
I am not sure - now I am getting an error message that says "Cant find
project or library". My code is below:

Sub PrintForms()
Dim StartRow
Dim EndRow
Dim Msg As String
Dim i

Sheets("1").Activate
With ActiveChart.PivotLayout.PivotTable.PivotFields("Mo nid")
For i = 1 To .PivotItems.Count
Range("B1") = PivotFields("Monid").Value
ActiveSheet.PrintOut
Next
End With


End Sub

How to loop through PivotTable
 

Sorry for the delay in response. Yes, the pivot table is on the same
worksheet. However, the data is on a separate sheet in the same workbook.

"Die_Another_Day" wrote:

This is because you haven't selected the Pivot Chart. Is the chart on a
separate page?

Die_Another_Day
ShadowVixen wrote:
I am not sure - now I am getting an error message that says "Cant find
project or library". My code is below:

Sub PrintForms()
Dim StartRow
Dim EndRow
Dim Msg As String
Dim i

Sheets("1").Activate
With ActiveChart.PivotLayout.PivotTable.PivotFields("Mo nid")
For i = 1 To .PivotItems.Count
Range("B1") = PivotFields("Monid").Value
ActiveSheet.PrintOut
Next
End With


End Sub

Resolved: How to loop through PivotTable
 

I finally got it - for anyone who is having the same challenge. The code is
below:

Sub PrintForms()
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem

' Set reference to pivot table
Set pt = ActiveSheet.PivotTables(1)
' Set reference to the pivot field
Set pf = pt.PivotFields("monid")

For Each pi In pf.PivotItems

Range("B1") = pi.Value
ActiveSheet.PrintPreview
'ActiveSheet.PrintOut
Next

End Sub