Solved

# Macro to list PivotTable filter selection

Posted on 2013-01-06
361 Views
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.

Thank you!!
Example.pdf
0
Question by:vegas86
• 2
• 2

LVL 43

Expert Comment

ID: 38749706
Yes it is possible. Can you upload a small sample file for "how to do it"?
0

Author Comment

ID: 38749864
Here you go!
example.xlsx
0

LVL 43

Accepted Solution

Saqib Husain, Syed earned 500 total points
ID: 38750044
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
``````
0

Author Closing Comment

ID: 38750081
FANTASTIC :)

Thank you so much!!!!!
0

## Featured Post

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…