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

 

[Trevor G]

What I have done here is create code to select visible cells and give it a defined Name.

If you incorporate this code and change the data source of your Pivots you can then use it as part of the Refresh Pivot Table.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Macro5()<br><SPAN style="color:#007F00">'The following script will create a name</SPAN><br><SPAN style="color:#007F00">'This can then be used on Filters to select all visible cells</SPAN><br><SPAN style="color:#007F00">'This can be used as moving data for Pivot Tables</SPAN><br><SPAN style="color:#007F00">'You would incoporate this into the Range Data the Pivot Uses</SPAN><br>Range("c3").CurrentRegion.SpecialCells(xlCellTypeVisible).Select<br>Selection.Name = "Database"<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[Whistler]

Hello Trevor,

I'm not sure I understand what this Makro do. It create "DataBase" in "C3" from all visible cells. Are the visible cells my PivotTable source?

 

[Whistler]

I thing I got it. I will use Date pickers to select two dates, then I will create code to generate all days between those two dates and put them in cells, then I will use your code to select them and give them a name. Later I can use this name as my pivottable filter.

Is that what you mean Trevor?

 

[Whistler]

Hi, I found on diffrent forum code and my modyfication you can find below.
When I change PivotField to "Line" and pivotItems to "line 1", "Line 2", "Line 3" ... and run it everything works great, but with dates is coming with fault : Unable to get the PivotItems property of the PivotField class

is this a problem with format, how I can avoid that?

Sub ChooseDate()
    Dim ws As Worksheet
    Set ws = Worksheets("Dashboard")
    Dim pt As PivotTable
    Dim pi As PivotItem
    Dim pf As PivotField
    Dim lngSortOrder As Long
    Dim strSortField As String
    
    Set pt = ws.PivotTables("PivotOutputPL")
    Set pf = pt.PivotFields("Date")
    pt.ManualUpdate = True
     ' remember settings
    strSortField = pf.AutoSortField
    lngSortOrder = pf.AutoSortOrder
     ' manual sort
    pf.AutoSort xlManual, pf.Name
    With pf
        .PivotItems("20/07/2011").Visible = False
        .PivotItems("21/07/2011").Visible = False
        .PivotItems("22/07/2011").Visible = False
        .PivotItems("23/07/2011").Visible = False
        .PivotItems("24/07/2011").Visible = False
        .PivotItems("(blank)").Visible = False
    End With
            
            pt.ManualUpdate = False
'
     ' re apply sort order
    pf.AutoSort lngSortOrder, strSortField
End Sub

thanks

 

[Whistler]

I got it! If I change database cells format to double and input to the pivot table to double all works great.

Now I have another problem, how to show items in the date field ( visible to true doesn't work ). It works with visible to false.

Thanks

 

[Whistler]

I have managed to resolved all my issues, below I put my code if anyone is interested. I have to date pickers, users choosing dates and when press button, all pivot tables are updated for this perid.

Sub ShowPeriod_Click()
Dim ws As Worksheet
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim DateA As Date
Dim DateB As Date
Dim DateAA As String
Dim DateBB As String
Set ws = Worksheets("Dashboard")
DateA = ws.Range("H3")
DateB = ws.Range("K3")
For Each pt In ws.PivotTables
Set pf = pt.PivotFields("Date")
DateAA = CDbl(DateA)
DateBB = CDbl(DateB)
On Error Resume Next
With pf
    .AutoSort xlManual, .SourceName
    For Each pi In pf.PivotItems
        pi.Visible = False
    Next pi
    Do While DateAA <= DateBB
    .PivotItems(DateAA).Visible = True
    DateAA = DateAA + 1
    Loop
    .AutoSort xlAscending, .SourceName
End With
Next pt
End Sub

But now if I choose two days perid it takes 2 minutes to update, if I choose a whole month or year it will takes ages to update.

Any chance to improve my code

Thanks

 

[Trevor G]

I have this code prepared for you. I have had to travel to London for work, so can only add this quickly.

I added some pointers (Comments).

I will read your thread about your other question tomorrow.

Quick note rather than use date (Excel thinks it needs to be USA format), try using Long

Dim dateA as Long

Sub updatePivot()
'The following sample code selects the Pivot Sheet - Change Name
'Move into a cell select the range and assign it a name
'Then move to the Sheet which will hold the data
'Assign this a different name
'Reselect the main sheet and then refresh the Pivot
'It also stops the copy command
'Assigns a date and user name to say who clicked the button and when
Application.ScreenUpdating = False
Sheets("Pivot Tables").Select
Range("C3").CurrentRegion.Select
Selection.Name = "Harry" 'Change name
Range("Harry").Copy 'Use same name if you have changed as above
Sheets("Pivot Results").Activate 'Change name
ActiveSheet.Paste
Range("A2").CurrentRegion.Select
Selection.Name = "UpdateHarry" 'Chnage name
Sheets("Pivot Tables").Select
Range("F28").Select
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
Range("k2") = Now() 'Change Cell if required
Range("l2") = Environ("UserName") 'Change Cell if required
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

 

[Whistler]

Thanks Trevor, I m out of the office untillt friday so I cannot test it now. what do you tkink about my code. i m happy with it but have some problems with the performance.

thanks again

 

[Trevor G]

Your code looks OK.

Perhaps if you look to switch the updating off before it runs and also the autocalculate it will perform quicker. See sample below with comments.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> ShowPeriod_Click()<br><SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet<br><SPAN style="color:#00007F">Dim</SPAN> pt <SPAN style="color:#00007F">As</SPAN> PivotTable<br><SPAN style="color:#00007F">Dim</SPAN> pf <SPAN style="color:#00007F">As</SPAN> PivotField<br><SPAN style="color:#00007F">Dim</SPAN> pi <SPAN style="color:#00007F">As</SPAN> PivotItem<br><SPAN style="color:#00007F">Dim</SPAN> DateA <SPAN style="color:#00007F">As</SPAN> Date<br><SPAN style="color:#00007F">Dim</SPAN> DateB <SPAN style="color:#00007F">As</SPAN> Date<br><SPAN style="color:#00007F">Dim</SPAN> DateAA <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> DateBB <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><SPAN style="color:#00007F">Set</SPAN> ws = Worksheets("Dashboard")<br>Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN> <SPAN style="color:#007F00">'Set up dating to Off Speeds things up</SPAN><br>Application.Calculate = xlCalculationManual <SPAN style="color:#007F00">' Stop the autocaluation</SPAN><br>DateA = ws.Range("H3")<br>DateB = ws.Range("K3")<br><SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> pt <SPAN style="color:#00007F">In</SPAN> ws.PivotTables<br><SPAN style="color:#00007F">Set</SPAN> pf = pt.PivotFields("Date")<br>DateAA = <SPAN style="color:#00007F">CDbl</SPAN>(DateA)<br>DateBB = <SPAN style="color:#00007F">CDbl</SPAN>(DateB)<br><SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br><SPAN style="color:#00007F">With</SPAN> pf<br>    .AutoSort xlManual, .SourceName<br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> pi <SPAN style="color:#00007F">In</SPAN> pf.PivotItems<br>        pi.Visible = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> pi<br>    <SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">While</SPAN> DateAA <= DateBB<br>    .PivotItems(DateAA).Visible = <SPAN style="color:#00007F">True</SPAN><br>    DateAA = DateAA + 1<br>    <SPAN style="color:#00007F">Loop</SPAN><br>    .AutoSort xlAscending, .SourceName<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">Next</SPAN> pt<br>Application.Calculate <SPAN style="color:#007F00">'Calculate all once processed</SPAN><br>Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#007F00">'Place Updating back on</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[Whistler]

Thanks very much TrevorI will let you know friday how it works,