Macro to list PivotTable filter selection

Hello,

Can someone please confirm whether or not it would be possible to create a macro that would look at the filtered range in a pivot table dropdown filter and then list in a cell for example "2012 to 2023". I have attached a picture to give an idea of what I mean. Generally with a pivot table filter, once you select more than 1 item it just shows "Multiple Items" whereas I would like to display the lowest and the highest years as a heading.

I have tried using a formula but I can't work it out.

Can anyone please help?

Thank you!!
Example.pdf
vegas86Asked:
Who is Participating?
 
Saqib Husain, SyedConnect With a Mentor EngineerCommented:
Try this

Right-click on the sheet tab name
select view code
paste this code in the VBA window
close the VBA window
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    Dim pitm As PivotItems, mx As Integer, mn As Integer, i As Integer
    Set pitm = Me.PivotTables(1).PivotFields("Year").PivotItems
    mx = 0
    mn = 9999
    For i = 1 To pitm.Count - 1
        If pitm(i).Visible = True Then
            If Val(pitm(i)) < mn Then mn = Val(pitm(i))
            If Val(pitm(i)) > mx Then mx = Val(pitm(i))
        End If
    Next i
    Range("d2").Value = mn & " - " & mx
End Sub

Open in new window

0
 
Saqib Husain, SyedEngineerCommented:
Yes it is possible. Can you upload a small sample file for "how to do it"?
0
 
vegas86Author Commented:
Here you go!
example.xlsx
0
 
vegas86Author Commented:
FANTASTIC :)

Thank you so much!!!!!
0
All Courses

From novice to tech pro — start learning today.