[Solved] VBA: ISO Smarter Pivot Table

    I have a couple of Pivot Tables that I'd like to show rows that meet changing criteria.


    I know how to go in and manually check whether or not a specific date is going to show up, but is there a way to uncheck any dates older than 90 days?


    Something like,


    If ("Date") < Today()-90 Then Uncheck selection


    Another pivot table I'd like to do this on is if the row is less than 30%, don't show that row.

    You could write a macro that would do that. Turn on the recorder and uncheck a few by hand and you will get an idea of the syntax to use. The macro could then be set to run whenever the workbook is opened.

    Ok - looking at just my date example, I want to uncheck dates older than 90 days. I decided to set five cells with formulae to return 90, 91, 92, 93, and 94 days old (=Today()-90, =Today()-91, etc). The macro should hide each of them. I started by declaring five variants, then setting them to the values found in those cells. Where I get stuck is hiding the values.


    Dim ninety0, ninety1, ninety2, ninety3, ninety4


    Sub Macro1()


    ninety0 = Sheets("Report Card").Range("AK1").Value
    ninety1 = Sheets("Report Card").Range("AK2").Value
    ninety2 = Sheets("Report Card").Range("AK3").Value
    ninety3 = Sheets("Report Card").Range("AK4").Value
    ninety4 = Sheets("Report Card").Range("AK5").Value


    With ActiveSheet.PivotTables("Tardy").PivotFields("Date")
    .PivotItems(I GET STUCK HERE).Visible = False
    End With


    End Sub


    I've tried putting my variable names there (where I'm getting stuck), but it isn't doing it. I feel like I'm close, but just not quite there.

    Try the following code. The on error is needed in case all of the dates are 90 or more days old. At least one value needs to remain visible
    [code]
    Sub Macro1()
    Dim p
    With ActiveSheet.PivotTables("Tardy").PivotFields("Date")
    On Error Resume Next
    For Each p In .PivotItems
    p.Visible = DateValue(p.Name) &gt; Now - 90
    Next p
    End With
    End Sub

    Wow, I was way off base. Nice code, Derk.


    Is there anyway to suspend calculating the fields while the For / Next loop runs? I tried putting these lines in, but the macro still calculates the pivot table after each value of p.


    Application.Calculation = xlManual


    With ActiveSheet.PivotTables("Tardy").PivotFields("Date")
    On Error Resume Next
    For Each p In .PivotItems
    p.Visible = DateValue(p.Name) &gt; Now - 90
    Next p
    End With


    Application.Calculation = xlAutomatic

    This one does not recalculate.


    I just hide the columns


    If search_day = "stp" Then
    Range("f2").Select
    For Count = 1 To column_count
    If Not ActiveCell &gt; Now() - search_period Then
    Selection.EntireColumn.Hidden = True
    column_to_hide = ActiveCell.Column
    End If

    If ActiveCell &gt; Now() - search_period Then
    Selection.EntireColumn.Hidden = False
    column_to_hide = ActiveCell.Column
    End If
    ActiveCell.Offset(0, 1).Select
    Next
    End If

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login