Unable to set the currentpage property of the pivotfield class

[jnonyx]

Hi there,

Leveraging some posts online, I have created a macro that should allow the user to make filter selections on one sheet while having the actual pivot table on another sheet.

Sub Update_Pivot()



Dim ws As Worksheet
Set ws = ActiveWorkbook.ActiveSheet

Dim wsname As String
wsname = ws.Range("a2").Value

Sheets("Pivot").Select

Dim xPTable As PivotTable
Dim xPField As PivotField
Dim xStr As String
Dim xPField2 As PivotField
Dim xStr2 As String

Set xPTable = Worksheets("Pivot").PivotTables("PivotTable1")
Set xPField = xPTable.PivotFields("Segment")
Set xPField2 = xPTable.PivotFields("Detail")



With xPTable
xPField.ClearAllFilters
xPField.CurrentPage = Sheets(wsname).Range("a4").Value
xPField2.ClearAllFilters
xPField2.CurrentPage = Sheets(wsname).Range("b4").Value
xPTable.RefreshTable
End With


Sheets(wsname).Select
Application.ScreenUpdating = True
End Sub

This worked perfectly so I implemented this to my real data with the following code and it errors out. The reason define and use "wsname" is that I want to make multiple copies of this sheet with different names so that the macro for updating the pivot table with the filter that I input would always refer to the latest copy of the data summary sheet. Please help me figure out what is wrong with my code as I have been stuck for hours and frustrated. Thanks in advance for your attention and help!


Sub Update_Pivot()




Dim ws As Worksheet
Set ws = ActiveWorkbook.ActiveSheet
Dim wsname As String
wsname = ws.Range("z4").Value

Sheets("Pivot").Select

Dim xPTable As PivotTable
Dim xPField As PivotField
Dim xPField2 As PivotField
Dim xPField3 As PivotField

Dim xStr As String
Dim xStr2 As String
Dim xStr3 As String

Set xPTable = Worksheets("Pivot").PivotTables("PivotTable1")
Set xPField = xPTable.PivotFields("Segment_Group")
Set xPField2 = xPTable.PivotFields("Package_Detail")
Set xPField3 = xPTable.PivotFields("Bureau_State")

With xPTable
xPField.ClearAllFilters
xPField.CurrentPage = Sheets(wsname).Range("z5").Value

xPField2.ClearAllFilters
xPField2.CurrentPage = Sheets(wsname).Range("z6").Value

xPField3.ClearAllFilters
xPField3.CurrentPage = Sheets(wsname).Range("z7").Value

xPTable.RefreshTable
End With


Sheets(wsname).Select


Application.screenupating = True
End Sub