?
Solved

Tell a PivotTable to deselect today and all future dates?

Posted on 2014-03-18
15
Medium Priority
?
178 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 23

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 2000 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

800 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