Populate combo box with week-ending dates, use to filter pivot table

[mib1019]

Hello all Excel Gurus!

I'm wanting to put a combo box on a worksheet that holds a pivot table of data organized by year, month and week ending dates, and use that combo box instead of a slicer or timeline.

Here's a picture of the table's first few rows:

My combo box should populate with the unique list from the table's Week-Ending Dates column. I'd also like it sorted newest to oldest.

Can you help me with code to populate the combo, then filter the pivot table by the result of the combo?

Thanks in advance!
MB

 

[BSALV]

hopefully not for your Mac.
2 macros, the 1st is to populate the combobox (if sorting wasn't necessary, it'd be less complicated).
The 2nd depends on the layout of your pivottable, so difficult to predict.

Sub Populate_Combo()
     Arr = Range("A2:A58").Value2                               'the range with your week ending days

     Set dict = CreateObject("scripting.dictionary")            'create a dictionary
     For i = 1 To UBound(Arr)                                   'loop through all days
          dict(Arr(i, 1)) = Array(100000# - Arr(i, 1), Format(Arr(i, 1), "mm/dd/yy"))     ' add to dictionary 100,000-datevalue and dateformat
     Next

     arr1 = Application.Sort(Application.Index(dict.items, 0, 0), 1)     'sort the items of the dictionary (=dates descending
     arr2 = Filter(Application.Transpose(Application.Index(arr1, 0, 2)), "a", 0, vbTextCompare)     'sort the items of the dictionary
     Blad1.ComboBox1.List = arr2
End Sub

Sub YourComboUpdate()
     With ActiveSheet.PivotTables(1).PivotFields("week ending")
          .ClearAllFilters
          .EnableMultiplePageItems = False
          .CurrentPage = Blad1.ComboBox1.Value
     End With
End Sub

 

[mib1019]

I'm going to try this. Can I reference the range of dates using its listobject identifiers. Want to make sure the populating takes in new data.

 

[BSALV]

lots of variants ...

     Set Arr = Sheets("blad1").ListObjects("tabel1").ListColumns("week ending").DataBodyRange.Value2                               'the range with your week ending days

 

[mib1019]

Thank you very much! Works perfectly.
MB