Change Pivot Table Filter Based on Cell Value when Pivot Table is from an external Databas

[Mark89]

I've been trying to link a cell value like a Date to an external database so that I can just pull the data for the specific date I want. I have the date criteria in a filter field. I was able to get the VBA code to work with a pivot table I created with data within the file, but the code does not seem to be talking to the external database pivot table. How can I change my code to make it tell the external database pivot table to change it's filter field based on what I type in a designated cell? Here is the code I have now. Thank you very much for the help!!!


Private Sub Worksheet_Change(ByVal Target As Range)
If Not Target.Address = Range("H6").Address Then Exit Sub
Dim PT As PivotTable
Dim ptItem As PivotItem

On Error Resume Next

For Each PT In Worksheets("Sheet1").PivotTables
With PT.PivotFields("Reporting Date.Fiscal Week")
If .EnableMultiplePageItems = True Then
.ClearAllFilters
End If
Set ptItem = .PivotItems(Target.Value)
If Not ptItem Is Nothing Then
.CurrentPage = Target.Value
End If
End With
Next


End Sub

[Mark89]

Bump.......

[cemregunay]

Please Login or Register  to view this content.

[Mark89]

Thank you so much!!!! I spent hours trying to figure this out today. I will try it at work tomorrow.

[Mark89]

Aw. I still can't get it to work. In the (rangename) I put ("H6") to specify the cell I want it to take the value I want to put into the Pivot Table filter field. For (PivotTableName) I put ("PivotTable8"). And for (FieldName) I put ("FiscalWeek").

[Mark89]

I am using OLAP pivot table.

[Mark89]

I have been searching for hours online how to convert the code to work with OLAP pivot table and can't find any solution...