Solved

Tell a PivotTable to deselect today and all future dates?

Posted on 2014-03-18
15
168 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
Comment Utility
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 20

Expert Comment

by:Ejgil Hedegaard
Comment Utility
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
Comment Utility
hgholt, FTW!
0
 

Author Comment

by:RWayneH
Comment Utility
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
Comment Utility
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
Comment Utility
Wouldn't Date be easier than trying to pull bits out of Now? ;)
0
 

Author Comment

by:RWayneH
Comment Utility
the hgholt suggestion is failing on line 3.  testing the first suggestion now.
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

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

Expert Comment

by:nutsch
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
EXCELlent!!  Thanks for the helps. -R-
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

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…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
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.

762 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

10 Experts available now in Live!

Get 1:1 Help Now