Solved

Tell a PivotTable to deselect today and all future dates?

Posted on 2014-03-18
15
172 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

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 …
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

810 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