VB Corrupting Pivot Tables
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 |
All times are GMT +1. The time now is 02:39 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com