I am trying to loop through all pages within a pivot table, my code for the
loop is below.

It seems to be looping and returning the page names, but it doesn't display
the relavant page on the spreadsheet.

I confess that I am a complete newbee to VB and would be gratefull for any
help

With ActiveSheet.PivotTables("PivotTable2").PivotFields ("Job Description")
For Each itm In .PivotItems
CurrentPage = itm
Next
End With

Thanks in advance to anyone who was going to comment, but with perseverence I
have resolved my problem.

"Jason" wrote:

I am trying to loop through all pages within a pivot table, my code for the
loop is below.

It seems to be looping and returning the page names, but it doesn't display
the relavant page on the spreadsheet.

I confess that I am a complete newbee to VB and would be gratefull for any
help

With ActiveSheet.PivotTables("PivotTable2").PivotFields ("Job Description")
For Each itm In .PivotItems
CurrentPage = itm
Next
End With

Hi

Based on my understanding, you create a pivottable on the an Excel
WorkSheet, but not the SpreadSheet Office Web Component.
For OWC issue, it would better to post in the newsgroup below.
microsoft.public.office.developer.web.components

The hierarchical structure of the Pivot Table is as below.
If we created one PivotTable on the Sheet1 in Excel Workbook. Then there
will be one PivotTable in the Sheet1.PivotTables collection.
Analogously, there are a collection PivotFields under the PivotTable, a
collection PivotItems under the PivotField.

Sub Test()
On Error Resume Next
Dim pv As PivotTable
Set pv = Sheet1.PivotTables(1)
Dim pf As PivotField
Dim pi As PivotItem
For Each pf In pv.PivotFields
Debug.Print pf.CurrentPageName
For Each pi In pf.PivotItems
Debug.Print " " & pi.Caption
Next
Next
End Sub

For every object's properties and methods we can refer to the vba help file.
It is located under the path below.
<Microsoft Office\OFFICE11\1033\VBAXL10.CHM

CurrentPageName Property
Returns or sets the currently displayed page of the specified PivotTable
report. The name of the page appears in the page field. Note that this
property works only if the currently displayed page already exists.
Read/write String.

Also I did not understanding your scenario very much. Can you post more
information what you do you mean by "the relavant page on the spreadsheet"?
Can you post the xls file you are using together with what you want to show
with VBA?

Thanks for your efforts!

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.