Filtering PivotTables between two dates

[Whistler]

Hi All,

I am designing Dashboard with several PivotCharts and I want them to change when user choose two dates. Charts will show data between those two dates. There is no Date in the Row so I cannot group or Fillter Date. Date is in the Fillter field. So I am able to select multiple dates but I dont know how to choose between to dates. Any Idea?

I will be much aprecieate.

Thanks

 

[Whistler]

I didnt find anything similar on the forum. Any Ideas?

 

[Whistler]

Are my questions too hard or too easy, because I never get any answer. Thanks

 

[Trevor G]

Oh dear, sorry to read you seem unhappy !

What about this as an idea, if you like it then we can look to convert it to code?

Consider using a named range for the Pivot Data

Then when you look at using your date range you can look to delete the Named Range and then re apply it based on the new data and refresh the Pivot

 

[Whistler]

Hello Trevor G,

Thanks for replaying. Are you meen create drop down menu for week, month, year, itp... , and when user choose one of them chart will show data for that period. I will think about that if I will not find idea for my imagination
What I realy want to do is to create calendar activeX controls and when user choose dates from both, it will show data on all pivotcharts between those two dates.

Sorry for my english, I tried my best

Thanks

 

[Trevor G]

Which version of Excel are you using?

If you create a UserForm in the VBA side you can add a Calendar control, so the users can select Start Date and End Date and then all the Pivots can be updated.

 

[Whistler]

I'm using 2007,

I have added Calendar controls to my spreadsheet, but how I can update charts with them?

 

[Whistler]

I have found that Calendar Control dosn't work on every pcs in my work, so I decided to use date chooseer with gives same output. But I still dont know how to control pivot tables from those dates.

thanks

 

[Whistler]

Good Morning everyone,

I come up with the code to select date in "Date" fillter but is coming with the error, if this will work I could loop that over every Pivot tables and dates.

ActiveSheet.PivotTables("PivotOutputPL").PivotFields("Date").CurrentPage = "(All)"

With ActiveSheet.PivotTables("PivotOutputPL").PivotFields("Date")
.PivotItems("22/07/11").Visible = True
End With

ActiveSheet.PivotTables("PivotOutputPL").PivotFields("Date"). _
EnableMultiplePageItems = True

Any ideas?

Many thanks

 

[Whistler]

I'm getting "Unable to get the PivotItems property of the PivotField class"