software

Hi!
I have a macro that switches pages in a pivot table, which works as long as
there is a matching page name for the string the macro wants to enter. When
there is no such page, the macro simply changes the name of the current page.
Do you have any ideas on how to avoid this? (I tried looping through the
PivotItems to verify my input but it seems as if old items are cached, I only
want to get the pages possible to choose in the current pivot table.)
Thankful for ideas!

/Marcus

You can prevent or remove old items from the pivot table:

www.contextures.com/xlPivot04.html

When changing pages, test for the item before setting the page. For example:

Sub ChangePivotPage()
'Test if Item exists
'before setting the CurrentPage to that item
On Error Resume Next
Dim ws As Worksheet
Dim pt As PivotTable
Dim pi As PivotItem
Dim str As String
Set ws = ActiveSheet
Set pt = ws.PivotTables(1)
str = ws.Range(quot;B1quot;).Value

On Error Resume Next

With pt.PageFields(quot;Regionquot;)
Set pi = .PivotItems(str)
On Error GoTo 0
If pi Is Nothing Then
.CurrentPage = quot;(All)quot;
Else
.CurrentPage = str
End If
End With

End SubMarcus Langell wrote:
gt; Hi!
gt; I have a macro that switches pages in a pivot table, which works as long as
gt; there is a matching page name for the string the macro wants to enter. When
gt; there is no such page, the macro simply changes the name of the current page.
gt; Do you have any ideas on how to avoid this? (I tried looping through the
gt; PivotItems to verify my input but it seems as if old items are cached, I only
gt; want to get the pages possible to choose in the current pivot table.)
gt; Thankful for ideas!
gt;
gt; /Marcus--
Debra Dalgleish
Excel FAQ, Tips amp; Book List
www.contextures.com/tiptech.htmlThat's also wonderful, thanks a million!

quot;Debra Dalgleishquot; wrote:

gt; You can prevent or remove old items from the pivot table:
gt;
gt; www.contextures.com/xlPivot04.html
gt;
gt; When changing pages, test for the item before setting the page. For example:
gt;
gt; Sub ChangePivotPage()
gt; 'Test if Item exists
gt; 'before setting the CurrentPage to that item
gt; On Error Resume Next
gt; Dim ws As Worksheet
gt; Dim pt As PivotTable
gt; Dim pi As PivotItem
gt; Dim str As String
gt; Set ws = ActiveSheet
gt; Set pt = ws.PivotTables(1)
gt; str = ws.Range(quot;B1quot;).Value
gt;
gt; On Error Resume Next
gt;
gt; With pt.PageFields(quot;Regionquot;)
gt; Set pi = .PivotItems(str)
gt; On Error GoTo 0
gt; If pi Is Nothing Then
gt; .CurrentPage = quot;(All)quot;
gt; Else
gt; .CurrentPage = str
gt; End If
gt; End With
gt;
gt; End Sub
gt;
gt;
gt; Marcus Langell wrote:
gt; gt; Hi!
gt; gt; I have a macro that switches pages in a pivot table, which works as long as
gt; gt; there is a matching page name for the string the macro wants to enter. When
gt; gt; there is no such page, the macro simply changes the name of the current page.
gt; gt; Do you have any ideas on how to avoid this? (I tried looping through the
gt; gt; PivotItems to verify my input but it seems as if old items are cached, I only
gt; gt; want to get the pages possible to choose in the current pivot table.)
gt; gt; Thankful for ideas!
gt; gt;
gt; gt; /Marcus
gt;
gt;
gt; --
gt; Debra Dalgleish
gt; Excel FAQ, Tips amp; Book List
gt; www.contextures.com/tiptech.html
gt;
gt;