Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dependent page items in pivot table | Excel Discussion (Misc queries) | |||
Data Field Items deleted when unchecked in Pivot table | Excel Discussion (Misc queries) | |||
Pivot Table - Dragging multiple items into data field | Excel Discussion (Misc queries) | |||
Pivot Table - untick all items in row field | Excel Discussion (Misc queries) | |||
Excel Pivot Table Appends a '2' to the field data items | Excel Discussion (Misc queries) |