Stepping through Pivot table page field items with VBA
 

The pivot table I'm working with in Excel 2000 has a different number of page
field items each time I refresh the data. I'm trying to find the way to step
through these, i.e. start with the first item then move onto the second item
and so on. While I can set the Currentpage to a specific value e.g "LN2" can
someone please tell me how to set it to the second item and then move on to
the third etc.

Stepping through Pivot table page field items with VBA
 

You can loop through the page items. For example:

'==============================
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
'================================

Tim Baker wrote:
The pivot table I'm working with in Excel 2000 has a different number of page
field items each time I refresh the data. I'm trying to find the way to step
through these, i.e. start with the first item then move onto the second item
and so on. While I can set the Currentpage to a specific value e.g "LN2" can
someone please tell me how to set it to the second item and then move on to
the third etc.


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Stepping through Pivot table page field items with VBA
 

Debra

Thanks for you helpful reply , the use of print preview has already improved
on the idea I was working on.

It hasn't quite solved my problem though because I didn't word my first
question exactly enough. I'm actually trying to step through the page field
items from the associad pivot chart. What I want to be able to do is just
step from the current page item to the next each time the macro is run i.e
go from 2 to 3, or 3 to 4. From your code I can see how to set the Name of
the current page but what I can't crack is how to get the index value of the
current page and then advance it by 1. I tried

ActiveChart.PivotLayout.PivotFields("SiteCode").Cu rrentPage = _
ActiveChart.PivotLayout.PivotFields("SiteCode"). _

PivotItems(ActiveChart.PivotLayout.PivotFields("Si teCode").CurrentPage.Index).Name

I'm a it confused (and new to VBA) but tried this after recording

ActiveChart.PivotLayout.PivotFields("SiteCode").Cu rrentPage = "ZF1"

when manually setting my page field to ZF1

Any hints would be greatfully appreciated. Thankyou for the useful web
page as well.

Tim Baker

You can loop through the page items. For example:

'==============================
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
'================================

Tim Baker wrote:
The pivot table I'm working with in Excel 2000 has a different number of page
field items each time I refresh the data. I'm trying to find the way to step
through these, i.e. start with the first item then move onto the second item
and so on. While I can set the Currentpage to a specific value e.g "LN2" can
someone please tell me how to set it to the second item and then move on to
the third etc.


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Stepping through Pivot table page field items with VBA
 

To move to the next item in the page field, you could use code similar
to the following:

'=============================
Sub GetNextPage()
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim iItem As Integer
Dim iNext As Integer
Dim str As String
Set pt = ActiveSheet.PivotTables(1)
Set pf = pt.PageFields("Rep")

For iItem = 1 To pf.PivotItems.Count
If pf.PivotItems(iItem).Visible = True Then
iNext = iItem + 1
Exit For
End If
Next iItem

If iNext pf.PivotItems.Count Then
iNext = 1
End If
str = pf.PivotItems(iNext).Name
pf.CurrentPage = pf.PivotItems(str).Name

End Sub
'================================

Tim Baker wrote:
Debra

Thanks for you helpful reply , the use of print preview has already improved
on the idea I was working on.

It hasn't quite solved my problem though because I didn't word my first
question exactly enough. I'm actually trying to step through the page field
items from the associad pivot chart. What I want to be able to do is just
step from the current page item to the next each time the macro is run i.e
go from 2 to 3, or 3 to 4. From your code I can see how to set the Name of
the current page but what I can't crack is how to get the index value of the
current page and then advance it by 1. I tried

ActiveChart.PivotLayout.PivotFields("SiteCode").Cu rrentPage = _
ActiveChart.PivotLayout.PivotFields("SiteCode"). _

PivotItems(ActiveChart.PivotLayout.PivotFields("Si teCode").CurrentPage.Index).Name

I'm a it confused (and new to VBA) but tried this after recording

ActiveChart.PivotLayout.PivotFields("SiteCode").Cu rrentPage = "ZF1"

when manually setting my page field to ZF1

Any hints would be greatfully appreciated. Thankyou for the useful web
page as well.

Tim Baker


You can loop through the page items. For example:

'==============================
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
'================================

Tim Baker wrote:

The pivot table I'm working with in Excel 2000 has a different number of page
field items each time I refresh the data. I'm trying to find the way to step
through these, i.e. start with the first item then move onto the second item
and so on. While I can set the Currentpage to a specific value e.g "LN2" can
someone please tell me how to set it to the second item and then move on to
the third etc.


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html