Solved

Tell a PivotTable to deselect today and all future dates?

Posted on 2014-03-18
15
174 Views
Last Modified: 2014-03-22
How do I edit this to say deselect today and any other future dates greater than today?

 With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
        "Planned Shipment Start")
        .PivotItems("3/18/2014").Visible = False
        .PivotItems("3/20/2014").Visible = False
    End With

Open in new window

0
Comment
Question by:RWayneH
15 Comments
 
LVL 39

Expert Comment

by:nutsch
ID: 39938166
Something like this?

dim pi as pivotitem

For Each pi In ActiveSheet..PivotTables("PivotTable1").PivotFields("Planned Shipment Start").PivotItems
    If CDate(pi.Name) >= Int(Now()) Then
        pi.Visible = False
    Else
        pi.Visible = True
    End If
Next pi

Open in new window

0
 
LVL 21

Expert Comment

by:Ejgil Hedegaard
ID: 39938221
Or using the filter

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Planned Shipment Start")
    .ClearAllFilters
    .PivotFilters.Add Type:=xlBefore, Value1:=Left(Str(Now), 10)
End With

Open in new window

0
 
LVL 39

Expert Comment

by:nutsch
ID: 39938235
hgholt, FTW!
0
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 

Author Comment

by:RWayneH
ID: 39938351
What is ,10 for after the (Now)?   This is not just for the two days in the example code,  It has to read equal to or greater than today.
0
 
LVL 39

Expert Comment

by:nutsch
ID: 39938363
Left(Str(Now), 10) converts the datetime of right now into a datestring (by removing the time part).

hgholt's code will put a datefilter on your pivot field that will only show dates before today.

My code will go through all the items in the field and hide all the items after today. Works too, but not as efficient as hgholt's filter approach.

Thomas
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 39939317
Wouldn't Date be easier than trying to pull bits out of Now? ;)
0
 

Author Comment

by:RWayneH
ID: 39939877
the hgholt suggestion is failing on line 3.  testing the first suggestion now.
0
 

Author Comment

by:RWayneH
ID: 39939881
and the first suggestion fails on the For Each pi  line, so neither are working.
0
 
LVL 39

Expert Comment

by:nutsch
ID: 39939903
Seems very strange, can you load a sample file on which the code would fail? What sort of error message are you getting?

Thomas
0
 

Author Comment

by:RWayneH
ID: 39939997
Here is the sample file that the failure is on.   Run Macro4
Book2.xlsm
0
 
LVL 39

Accepted Solution

by:
nutsch earned 500 total points
ID: 39940015
There's a blank field in your data set that throws off the date filters. That update on my code worked on your data set:

Dim pi As PivotItem

For Each pi In ActiveSheet.PivotTables("PivotTable1").PivotFields("Planned Shipment Start").PivotItems
    If IsDate(pi.Name) Then
        If CDate(pi.Name) >= date() Then
            pi.Visible = False
        Else
            pi.Visible = True
        End If
    End If
Next pi

Open in new window

0
 

Author Comment

by:RWayneH
ID: 39940124
Worked great!!  Could I extend the question a bit?  At the end of the process it selects the list, however the header "Row Labels" is selected also.  How can I not have that as part of the selected area?  Thanks.
0
 

Author Comment

by:RWayneH
ID: 39940137
I forgot... and at the bottom of the pivot table there is a "Grand Total"  I do not need that one either.  Just the results is what I need to copy out of the table.  Is that a setting?  or can the one that takes the header out also take the footer out?
0
 
LVL 39

Expert Comment

by:nutsch
ID: 39940159
Instead of    
ActiveCell.Offset(2, 0).Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select

Open in new window


Use

ActiveSheet.PivotTables("PivotTable1").RowRange.Offset(1).Resize(ActiveSheet.PivotTables("PivotTable1").RowRange.Rows.Count - 2).Select

Open in new window

0
 

Author Closing Comment

by:RWayneH
ID: 39947771
EXCELlent!!  Thanks for the helps. -R-
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Can't get rid of Update Links message in Excel 2013 8 33
Need conditional formating when doubles 14 21
Excel Sheet Data Finding 14 19
Slow calculation 2 21
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question