CODEWITHSUNDEEP
excelvbapivot-table
Aaron Rogers
Aaron Rogers

Reputation: 3

Excel VBA, Sort Dates via list

I am trying to sort a list of Sales Orders (on a pivot table) and filter to find the entries that are scheduled to ship on a holiday (a list of ~12 dates).

Considering this pivot table is Dynamic, but the list of dates is not, is there a way to apply these filters via a VBA macro, so i do not have to do so manually?

I have tried using the Macro recorder, but it just sets all of the existing dates to

actual list came up much, much, larger

With ActiveSheet.PivotTables("SalesOrders").PivotFields("Due Date")
    .PivotItems("2/28/2019").Visible = False
    .PivotItems("5/22/2019").Visible = False
    .PivotItems("7/17/2019").Visible = False
    .PivotItems("7/18/2019").Visible = False
    .PivotItems("8/7/2019").Visible = False
    .PivotItems("8/16/2019").Visible = False
    .PivotItems("8/19/2019").Visible = False
    .PivotItems("8/22/2019").Visible = False
    .PivotItems("8/28/2019").Visible = False
    .PivotItems("8/29/2019").Visible = False
    .PivotItems("8/30/2019").Visible = False

End With
With ActiveSheet.PivotTables("SalesOrders").PivotFields("Due Date")
     .PivotItems("5/25/2020").Visible = True
     .PivotItems("7/3/2020").Visible = True
     .PivotItems("7/6/2020").Visible = True
End With

This doesn't really work, because the dates will change... I'm not really sure how to progress, any help would be welcome.

Upvotes: 0

Views: 64

Answers (1)

Jeff1265344
Jeff1265344

Reputation: 123

If you can add a new column to your data, you won't need to use VBA, you can get this incorporated in your pivot table. Add a new sheet that contains only the dates of the holidays in one column and True in the second column. Then add a new column to your Sales Order data and in that column use the Vlookup function to see if the date in your Sales Orders date is found in the list of holidays. You can add this new column to your pivot table as a filter. The screen shot below shows an example. Vlookup Example

Upvotes: 0

Related Questions