What I want: I have two different Pivot Tables using filters. Both filter on the Date but have different source data (in which the dates partially overlap). I want the second filter to adopt the same value (date) as the first filter whenever the first filter changes.
What I tried: I tried an endless amount of solutions found on the internet, however none of them works. The ones that came closest to working:
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Sheets("Daganalyse_tables").PivotTables("PivotTable1").PivotFields("Date").ClearAllFilters
Sheets("Daganalyse_tables").PivotTables("PivotTable1").PivotFields("Date").CurrentPage = Range("D2").Text
End Sub`
Where D2 is the location of the first filter. This keeps giving me a 'Run-time error 1004: Application-defined or object-defined error'. I tried this one in quite a lot of variations, keeps giving me error 1004.
Second thing I tried was even simpler:
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Sheets("Daganalyse_tables").Range("F1") = Range("D2")
End Sub
Where F1 is the location of the second filter. This one tells me that '41667 is not an item of this field', in other words it converts the date to its number. This still happens when using Format() or entering a random date as a String.
Does anybody have an idea on how to accomplish this? It appears to be very easy, but Excel seems to have a problem with it (tried it in 2013 and 2010). Thanks in advance!
