Prev: Tracking an email when sent directly from Excel
Next: stop VBA in solver when maximum iterations reached
From: kernel on 18 May 2010 20:27
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...
From: Javed on 19 May 2010 03:34
On May 19, 5:27 am, kernel <ker...(a)discussions.microsoft.com> 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.


From: kernel on 20 May 2010 22:27
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?
 | 
Pages: 1
Prev: Tracking an email when sent directly from Excel
Next: stop VBA in solver when maximum iterations reached