Change Pivot Table filter based on value on a different tab

[angil0126]

I have a workbook that contains a table (populated using ODBC connection to my ERP database), and two hidden pivot tables. There is code that copies the pivot tables and pastes as values on a different spreadsheet. This is done because there are some calculations that needed to be added that a pivot table can't handle. I need to change the pivot tables filter to the month that is entered on the main table. So a user would type '03/01/2023' and the two pivot tables would change their filter and the vba code that copies/pastes/etc would run afterwards. So I need to do all of this on a change of the values in Sheet 1. I can't seem to get it to work. Can anyone assist?
The code for copy/paste/etc works fine, it's just the changing of the filter on the two pivot tables. It is executing the vba, but I'm getting an error "Application-defined or object-defined error" The debug highlights the last line: myPivotField.CurrentPage = filterValue.

Sheet1 Name = "Trend" the field where the user is entering the month is B1
Sheet2 Name = "By Area Pivot" - This has PivotTable1 Filter filed = WIPMonth
Sheet 3 Name = "By PM Pivot" - This has PivotTable2 Filter filed = WIPMonth

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$B$1" Then

    Dim myPivotField As PivotField
    Dim myPivotField2 As PivotField
    Dim filterValue As String
   
    Set myPivotField = Sheets("By Area Pivot").PivotTables("PivotTable1").PivotFields("WIPMonth")
    filterValue = ActiveWorkbook.Sheets("Trend").Range("B1").Value
   

    myPivotField.CurrentPage = filterValue
   

   
End If

End Sub

 

[jkpieterse]

Why do the calculations have to point to a static copy of the pivot table?

 

[angil0126]

I'm not sure what you mean. There aren't any calculations, just a field where a user will update the month. The filters need to change when that field changes

 

[jkpieterse]

Don't convert the value to a string first, instead:

myPivotField.CurrentPage = ActiveWorkbook.Sheets("Trend").Range("B1").Value