Hi,
I already got some help in the pivot forum but it's not working quite yet. And without any coding experience, I need support to finalise...
I have two pivots ("PivotDetails" and "PivotSummary") where I select the year I want to analyse with the help of a slicer. I then need two other pivots ("PivotDetails_PY" and "PivotSummary_PY") to automatically select the previous year.
I have already created a calculation in D2 showing the previous year, so D2 value should be selected as "Year" for "PivotDetails_PY" and "PivotSummary_PY".
The worksheet is called "PIVOT".
I got below code but a) it selects "All" years, not the value from D2 and b) I need to add the "PivotSummary_PY" to do the same.
Maybe one issue is that the years are formatted as text in my incoming report but are a number when calculated in D2??? Unfortunately, I cannot change the incoming report without a bit of time consuming manual effort every week - which I would like to avoid. So if this is an issue, this should ideally be somehow solved in the code as well.
Can anyone help?Private Sub Worksheet_Change(ByVal Target As Range) Dim PT As PivotTable Dim f As PivotField Dim Str As String On Error Resume Next If Intersect(Target, Range("D2:D2")) Is Nothing Then Exit Sub Application.ScreenUpdating = False Set PT = Worksheets("PIVOT").PivotTables("PivotDetails_PY") Set f = PT.PivotFields("Year") Str = Target.Text f.ClearAllFilters f.CurrentPage = Str Application.ScreenUpdating = True End Sub
Bookmarks