Link VBA Pivot to a cell range for filter update

[harriers]

Hi all,

I have a vba script that makes a pivot table, works well, but I need to link the pivot table filter to a 'namedrange' cell on a spreadsheet. I have found the code below on google but can't seem to get it to work, when the cell is updated the pivot does not refresh??.... any idea's??


Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim ws As Worksheet
Dim pt As PivotTable
Dim pi As PivotItem
Dim strField As String

strField = "Period (01/MM/YYYY)"

On Error Resume Next
Application.EnableEvents = False
Application.ScreenUpdating = False

If Target.Address = Range("SelectDate").Address Then

' For Each ws In ThisWorkbook.Worksheets
Set ws = Me
For Each pt In ws.PivotTables
With pt.PageFields(strField)
For Each pi In .PivotItems
If pi.Value = Target.Value Then
.CurrentPage = Target.Value
Exit For
Else
.CurrentPage = "(All)"
End If
Next pi
End With
Next pt
' Next ws

End If

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

[mikeTRON]

What exactly are you trying to accomplish?