Pivot Table Page Field value from cell in another worksheet

[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 have received some help for this problem and got this far -

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?

 

[Roger Govier]

Hi

How many Pivot Tables do you have on worksheet b?
If there is only 1, then change the code to
PivotTables(1)
in both instances within the code

Alternatively, be specific and use
PivotTables("PivotTable2")
in both instances

 

[kernel]

Hi,

Thanks for the response. I changed the code to -

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = Range("CustomerNumber").Address Then
Me.PivotTables("PivotTable2").PivotFields("Account Number").CurrentPage
= Range("CustomerNumber").Value
Me.PivotTables("PivotTable2").PivotCache.Refresh
End If
End Sub

.....but I am still getting the "method 'PivotTables' of object '_Worksheet'
failed" error.

 

[Roger Govier]

Hi

If you want to mail me a copy of the workbook, I will take a look.
Mail to
roger at technology4u dot co dot uk
Change the at to @ and dots to . and remove spaces, to make a valid
eamil address