Avoid renaming page fields
 

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

Avoid renaming page fields
 

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

http://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("B1").Value

On Error Resume Next

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

End Sub


Marcus Langell wrote:
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


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

Avoid renaming page fields
 

That's also wonderful, thanks a million!

"Debra Dalgleish" wrote:

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

http://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("B1").Value

On Error Resume Next

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

End Sub


Marcus Langell wrote:
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


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