Select all dates on a slicer between two dates

[dancing-shadow]

Hi all,

I have a sheet with around 30 pivots on it displayed in two columns - pivots in the left columns display data per day, data in the right columns display the same data but per month

I have a slicer that links to all the day pivots (so about 15 pivots), that I need to amend daily to show the date range between today and today - 30. Basically I'm unselecting the last selected item on the slicer list, and selecting todays date. The raw data behind the pivots is updated daily to include the latest lines, so is ever-increasing in size. Because of this constant expansion of data, I can't filter one of the pivots directly, as to do that it firsts clears all the filters which expands the pivot to fit all the dates into it - with the monthly pivots right next door, eventually it'll bump into it and create an error!

Having tried out the macro recorder, I get a long list of actions to select/deselect each slicer item, which is no good to me due to the increasing size of the list each day. After some searching about I came across this code which I've tailored for my own file and what I want to achieve:

Please Login or Register  to view this content.

My issue is that I can't seem to get the code to check if a date is greater than/equal to, presumably because it's in string format it's like comparing text against text and therefore won't treat it as a number. I get that, but how can I look at the slicer item as a date, rather than as text so I can check the <= / >= part?

[xlnitwit]

Hi,

You might try using the actual date values instead

Please Login or Register  to view this content.

[Richard Buttrey]

Hi,

Can you upload the workbook and manually add an example of what you want to see.
I must admit it's unusual to hear that you have a workbook with 30 pivot tables since one of the principal benefits of a PT is its ability to show different views of the same data at the click of a slicer or the drag and drop of a field. Personally I've never seen an operational workbook with more than three PTs.

Neither is it obvious why you are showing Dates in the Column labels area rather than the rows area. This would stop your PT expanding to the right.

As to being able to see just the last month have you considered adding an extra column to your data that identifies all dates in the last month and use this field in a Slicer or PT filter to pick out the last month's data. e.g.

Please Login or Register  to view this content.

Are you using a dynamic range name for your data and using the range name as the source for your PT so that you don't need to worry about constantly changing the PT range. All you then need to do is to use a Sheet Activate event macro to automatically refresh the PT when the PT sheet is activated.

Upload the workbook if you feel there's still a problem.

[dancing-shadow]

xlnitwit - thank you so much, this is perfect!! Exactly what I was after

Richard - to clear up your queries, the layout of many tables and a daily (rolling 30 days)/monthly view is how the end user wants it displayed, not my choice at all, but they pay my wages so I can't complain too much! This is also the same for the dates in column labels as opposed to rows. It's how they've always had it, and how they like it. Changes have been suggested but never agreed to, so this format remains unchanged.
The original tables were formulae connected to pivots on a slave sheet, but it was messy and took forever to process all the calculations, so I changed it all for pivots.
I tried using a slave column in my data, to throw a simple Y/N for each date within my specific date range, but I found that the pivots would either show all dates whether they were filtered on the Y or not, or it would show the specified dates but no data for any date that didn't exist. The pivot option for 'Show all items with no data' cannot also be filtered on - it's a one or the other type deal, which doesn't work for what I need.

[xlnitwit]

You're welcome.