Filter a Pivottable based on a value of a cell (formula) in another worksheet

[Romera]

Hi!

To sketch the sheet: The homepage is created as a dashboard with a pivottable which should contain the Names if the column Weeknumber is equal to the current weeknumber. So, the data is stored on another worksheet than where the pivottable is shown. The data in the column Weeknumber is calculated based on data in another column called Date, so it's a formula.

I want to have 1 cell with the formula =WEEKNUMBER(TODAY()) as the filter value so that this pivottable updates automatically every week.

The code shown below is not working for me. Unfortunately, it gets stuck in the following line:

 Set Field = pt.PivotFields("Weeknumber")

Code:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Intersect(Target, Worksheets("Dashboard").Range("L22:L23")) Is Nothing Then Exit Sub

    'Set the Variables to be used
    Dim pt As PivotTable
    Dim Field As PivotField
    Dim NewCat As String
    
    'Amend to suit data
    Set pt = Worksheets("Dashboard").PivotTables("Pivottable1")
    Set Field = pt.PivotFields("Weeknumber")
    NewCat = Worksheets("Dashboard").Range("L22").Value
    
    'This updates and refreshes the PIVOT table
    With pt
    Field.ClearAllFilters
    Field.CurrentPage = NewCat
    pt.RefreshTable
    End With

End Sub

I hope someone can help me with this!

All the best,
Romera