Hi,

I have found that using the following code I am able to corrupt the data in
my pivot table:

On Error Resume Next
ActiveSheet.PivotTables("PTMonthlyAnalystSalesPA") .PivotFields("PA").CurrentPage = ActiveSheet.Range("B1").Value
If Err Then
ActiveSheet.PivotTables("PTMonthlyAnalystSalesPA") .PivotFields("PA").CurrentPage = "(blank)"

What I have found is that values in the PA page field are changed, even
though the actual data source has not. For example, my list used to contain
the name "Alex", but this is replaced with "Steve". In the data source
"Steve" is not referenced in the SA column. If I select Steve, it pulls the
data for Alex!

If you refresh this doesn't make a difference. If you replace the pivot
table with an identical table it returns to normal, until I start running
this code again.

Has anyone encountered this problem and can advise how I can avoid this?

Thanks,
Ra

You could test for the item, before setting the page, e.g.:

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("PA")
Set pi = .PivotItems(str)
On Error GoTo 0
If pi Is Nothing Then
.CurrentPage = "(All)"
Else
.CurrentPage = str
End If
End With

End Sub


RestlessAde wrote:
Hi,

I have found that using the following code I am able to corrupt the data in
my pivot table:

On Error Resume Next
ActiveSheet.PivotTables("PTMonthlyAnalystSalesPA") .PivotFields("PA").CurrentPage = ActiveSheet.Range("B1").Value
If Err Then
ActiveSheet.PivotTables("PTMonthlyAnalystSalesPA") .PivotFields("PA").CurrentPage = "(blank)"

What I have found is that values in the PA page field are changed, even
though the actual data source has not. For example, my list used to contain
the name "Alex", but this is replaced with "Steve". In the data source
"Steve" is not referenced in the SA column. If I select Steve, it pulls the
data for Alex!

If you refresh this doesn't make a difference. If you replace the pivot
table with an identical table it returns to normal, until I start running
this code again.

Has anyone encountered this problem and can advise how I can avoid this?

Thanks,
Ra


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

Hi

Thanks for this. It solved my problem and I appreciate your help.

Regards,
Ra

"Debra Dalgleish" wrote:

You could test for the item, before setting the page, e.g.:

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("PA")
Set pi = .PivotItems(str)
On Error GoTo 0
If pi Is Nothing Then
.CurrentPage = "(All)"
Else
.CurrentPage = str
End If
End With

End Sub


RestlessAde wrote:
Hi,

I have found that using the following code I am able to corrupt the data in
my pivot table:

On Error Resume Next
ActiveSheet.PivotTables("PTMonthlyAnalystSalesPA") .PivotFields("PA").CurrentPage = ActiveSheet.Range("B1").Value
If Err Then
ActiveSheet.PivotTables("PTMonthlyAnalystSalesPA") .PivotFields("PA").CurrentPage = "(blank)"

What I have found is that values in the PA page field are changed, even
though the actual data source has not. For example, my list used to contain
the name "Alex", but this is replaced with "Steve". In the data source
"Steve" is not referenced in the SA column. If I select Steve, it pulls the
data for Alex!

If you refresh this doesn't make a difference. If you replace the pivot
table with an identical table it returns to normal, until I start running
this code again.

Has anyone encountered this problem and can advise how I can avoid this?

Thanks,
Ra


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

You're welcome. Thanks for letting me know that it solved the problem.

RestlessAde wrote:
Hi

Thanks for this. It solved my problem and I appreciate your help.

Regards,
Ra

"Debra Dalgleish" wrote:


You could test for the item, before setting the page, e.g.:

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("PA")
Set pi = .PivotItems(str)
On Error GoTo 0
If pi Is Nothing Then
.CurrentPage = "(All)"
Else
.CurrentPage = str
End If
End With

End Sub


RestlessAde wrote:

Hi,

I have found that using the following code I am able to corrupt the data in
my pivot table:

On Error Resume Next
ActiveSheet.PivotTables("PTMonthlyAnalystSalesP A").PivotFields("PA").CurrentPage = ActiveSheet.Range("B1").Value
If Err Then
ActiveSheet.PivotTables("PTMonthlyAnalystSalesP A").PivotFields("PA").CurrentPage = "(blank)"

What I have found is that values in the PA page field are changed, even
though the actual data source has not. For example, my list used to contain
the name "Alex", but this is replaced with "Steve". In the data source
"Steve" is not referenced in the SA column. If I select Steve, it pulls the
data for Alex!

If you refresh this doesn't make a difference. If you replace the pivot
table with an identical table it returns to normal, until I start running
this code again.

Has anyone encountered this problem and can advise how I can avoid this?

Thanks,
Ra


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