Solved

Tell a PivotTable to deselect today and all future dates?

Posted on 2014-03-18
15
169 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
 

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
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…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

863 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

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now