Change Pivot Table Page Value
 

I have looked through the other posts on the subject and the combination of
my low skills in VB and the other solutions not exactly covering my situation
means I still need to ask for help.

Scenario:

Worksheet a = "Scorecard"
FieldName = "CustomerNumber"

Worksheet b = "Products Resume"
PivotTable = "PivotTable2"
PivotTable Page Field = "Account Number"

When CustomerNumber value on worksheet "Scorecard" is changed I need the
Pivot Table Page field "Account Number" to use this value to filter the pivot
table.

I found the below code which looks like it may be close to what I need but
my lack of VB knowledge meant I could not adapt it -

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("SelDept").Address Then
Me.PivotTables(1).PivotCache.Refresh
End If
End Sub

Help please...

Change Pivot Table Page Value
 

On May 19, 5:27*am, kernel wrote:
I have looked through the other posts on the subject and the combination of
my low skills in VB and the other solutions not exactly covering my situation
means I still need to ask for help.

Scenario:

Worksheet a = "Scorecard"
FieldName = "CustomerNumber"

Worksheet b = "Products Resume"
PivotTable = "PivotTable2"
PivotTable Page Field = "Account Number"

When CustomerNumber value on worksheet "Scorecard" is changed I need the
Pivot Table Page field "Account Number" to use this value to filter the pivot
table.

I found the below code which looks like it may be close to what I need but
my lack of VB knowledge meant I could not adapt it -

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("SelDept").Address Then
* * Me.PivotTables(1).PivotCache.Refresh
End If
End Sub

Help please...


Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("SelDept").Address Then
Me.PivotTables(1).PivotFields("Account
Number").CurrentPage=Range("SelDept").Value
Me.PivotTables(1).PivotCache.Refresh
End If
End Sub

I have expected that Range("SelDept") avlue will decide the page of
pivottable.

Change Pivot Table Page Value
 

Thanks for the response. Based on your suggestion and with my fields added
the code looks like this -

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("CustomerNumber").Address Then
Me.PivotTables(2).PivotFields("Account Number").CurrentPage =
Range("CustomerNumber").Value
Me.PivotTables(2).PivotCache.Refresh
End If
End Sub

.....but I get the error

"method 'PivotTables' of object '_Worksheet' failed

Any ideas?

Thanks for the response.
 

You should include the error message as a clue. :-)
I am also learning, but first of all you have chosen PivotTable(2), which means it is the 2nd PivotTable in your worksheet. If that's right, fine.
(If you want to be sure, do
For Each pvtable In Worksheets("Products Resume")
msgbox pvtable.Name
Next
Second, I do not know pivot table field properties very well, but it seems odd to me for your request to change a property called CurrentPage - but hey, you probably know better than me.
But if you want to see all the properties, do Help for "PivotField Object", then click PivotField Object, then select Properties.
What you can also try is selectively increasing your references from a minimal to the end result, eg., add these lines before the 2 lines between the If and End if, then click debug when it crashes to see which line has a problem:
If ...
MsgBox Me.Name
MsgBox Me.PivotTables(2).Name
MsgBox Me.PivotTables(2).PivotFields("Account Number").CurrentPage
MsgBox Range("CustomerNumber").Value
... (your existing code)
End If
The line it crashes on should give you a better idea what Excel can't see.
Then explore further.
For example, if it crashes on MsgBox Me.PivotTables(2).Name, try MsgBox Me.PivotTables(1).Name
Hope this helps.


On Tuesday, May 18, 2010 8:27 PM kernel wrote:

I have looked through the other posts on the subject and the combination of
my low skills in VB and the other solutions not exactly covering my situation
means I still need to ask for help.

Scenario:

Worksheet a = "Scorecard"
FieldName = "CustomerNumber"

Worksheet b = "Products Resume"
PivotTable = "PivotTable2"
PivotTable Page Field = "Account Number"

When CustomerNumber value on worksheet "Scorecard" is changed I need the
Pivot Table Page field "Account Number" to use this value to filter the pivot
table.

I found the below code which looks like it may be close to what I need but
my lack of VB knowledge meant I could not adapt it -

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("SelDept").Address Then
Me.PivotTables(1).PivotCache.Refresh
End If
End Sub

Help please...


On Wednesday, May 19, 2010 3:34 AM Javed wrote:

of
tion
ivot
t


Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address =3D Range("SelDept").Address Then
Me.PivotTables(1).PivotFields("Account
Number").CurrentPage=3DRange("SelDept").Value
Me.PivotTables(1).PivotCache.Refresh
End If
End Sub

I have expected that Range("SelDept") avlue will decide the page of
pivottable.


On Thursday, May 20, 2010 10:27 PM kernel wrote:

Thanks for the response. Based on your suggestion and with my fields added
the code looks like this -

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("CustomerNumber").Address Then
Me.PivotTables(2).PivotFields("Account Number").CurrentPage =
Range("CustomerNumber").Value
Me.PivotTables(2).PivotCache.Refresh
End If
End Sub

....but I get the error

"method 'PivotTables' of object '_Worksheet' failed

Any ideas?