Controling an Excel Pivot table filter from a cell value

[Denised]

I have a excel worksheet that is connect to an SQL Service Cube.

On one tab I have all my customers, what I was trying to do use copy the customer number into a cell that the product tab would then filter on all product sales sold to that customer.

I can get this to work if I am not pulling the data for the Cube, using a small subset of the data, but it does not work when using the cube.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)


If Intersect(Target, Range("C8")) Is Nothing Then Exit Sub


Dim pt As PivotTable
Dim Field As PivotField
Dim NewCat As String


Set pt = Worksheets("Product").PivotTables("ProductData")
Set Field = pt.PivotFields("Customer Name")
NewCat = Worksheets("Customer").Range("C8").Value

'This updates and refreshes the PIVOT table
With pt
Field.ClearAllFilters
Field.CurrentPage = NewCat
pt.RefreshTable
End With

End Sub


Does anyone have any ideas on have to get this to work against a cube?

Thanks!