Change Pivot Table Page Value

[kernel]

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

 

[Javed]

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.

 

[kernel]

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?