VBA code to select the latest date from a pivot table field

[Ajit Kiran]

Hello guys!

Have just joined and feel excited to be a part of this great forum.

At the outset, would like to thank everyone in advance for all the support and guidance.

I'm looking for a VB code which selects only the latest date from a given range i.e. Pivot field range and select only the latest and unselects the rest. For eg., a date range from 3rd Sept to 9th Sept (being the latest chronologically), the VB code should only select 9th Sept (of course the date changes as days pass by and should be able to select only the latest).

I've attached a spreadsheet herewith.

Thanks a ton in advance!

Ajit Kiran

 

[Sam Mathai Chacko]

Try this

Sub MaxDatePivot()

    Dim pfiPivFldItem As PivotItem
    Dim dtmDate As Date
    With Worksheets("Sheet1").PivotTables(1)
        .PivotCache.Refresh
        .ClearAllFilters
        With .RowRange
            dtmDate = Evaluate("MAX(IF(ISNUMBER(" & .Address(0, 0) & ")," & .Address(0, 0) & ",))")
        End With
        For Each pfiPivFldItem In .PivotFields("End Date").PivotItems
            pfiPivFldItem.Visible = (CDate(pfiPivFldItem.Value) = CLng(dtmDate))
        Next pfiPivFldItem
    End With
    
End Sub

 

[Sam Mathai Chacko]

I just realized that your data also has blank rows. Please remove those. If they are absolutely necessary, then you need to use this

Sub MaxDatePivot()

    Dim pfiPivFldItem As PivotItem
    Dim dtmDate As Date
    With Worksheets("Sheet1").PivotTables(1)
        .PivotCache.Refresh
        .ClearAllFilters
        With .RowRange
            dtmDate = Evaluate("MAX(IF(ISNUMBER(" & .Address(0, 0) & ")," & .Address(0, 0) & ",))")
        End With
        For Each pfiPivFldItem In .PivotFields("End Date").PivotItems
            If pfiPivFldItem.Value = "(blank)" Then
                pfiPivFldItem.Visible = False
            Else
                pfiPivFldItem.Visible = (CDate(pfiPivFldItem.Value) = CLng(dtmDate))
            End If
        Next pfiPivFldItem
    End With
    
End Sub

 

[Ajit Kiran]

Hi Sam!!

Thanks a ton for the reply!! The (2nd) code actually worked like a charm!! Brilliant!!

Thanks a bunch again! I'm in fact learning VBA at this juncture and kind of immensely excited lookin at the way VBA working wonders! All along I've only been recording Macros and trying to understand the nitty-gritties through that process cos I'm not into an IT arena per se but strongly interested in coupling up my financial acumen with core VB skills like this. And must say, people like you are helping me a great deal getting there

Hope it wouldn't be too much of me to ask for yet another small favor with regards to the attached file. I'm looking to have a value being inserted when a condition is being met. For eg., in the attached file, values under Col F i.e Header Comment would have to turn / get inserted as "Same Day Cancel" when I filter on the header 'd' for a value 'C' in col D.

All I know is that the above can be accomplished through loops (correct me if I'm wrong, pls) and becomes a bouncer when I attempt to understand such loops as & when I read somewhere!

Your help is immensely appreciated. Thanks again in advance. Also, request you to pls share any material on VB to pick things up at a relatively faster rate. I've downloaded a number of soft copies on VBA but the pedagogy isn't too appealing to me unfortunately.

Thanks a ton for your help again!

Kind Regards,
Ajit Kiran
ajitkiran87@gmail.com

 

[Sam Mathai Chacko]

Why do you want it to happen when you 'filter' something? Why not have a button, which when clicked, changes all values in Col F, where Col D = "C", to turn to that text ?

 

[Ajit Kiran]

Umm...not necessarily it should be done that way. The reason I was looking for such a kind of code is because I've put in a code already and it's just this bit which I couldn't append with i.e this part in the other big process.

Yes, like you mentioned - a button when clicked, the text appears in Col F where Col D = "C" should definitely help.

Thanks again!

 

[Ajit Kiran]

Hey Sam!

Sorry, jus realised I'd need a bit of tweaking the PivotFld Macro u've given me...I'd in fact need the last item in the fields to be selected. I get multiple options like
1. All
2. (blank)
3. 0
4. Date eg. 8/15/2013

And I only want the date to be selected among the 4 fields above. Would it be possible to relook pls?

Also, if I have 3 pivots of the same design how do I include all 3 pivots in the code?

Thanks again in advance!

 

[VBA Learner]

I just realized that your data also has blank rows. Please remove those. If they are absolutely necessary, then you need to use this

Sub MaxDatePivot()

    Dim pfiPivFldItem As PivotItem
    Dim dtmDate As Date
    With Worksheets("Sheet1").PivotTables(1)
        .PivotCache.Refresh
        .ClearAllFilters
        With .RowRange
            dtmDate = Evaluate("MAX(IF(ISNUMBER(" & .Address(0, 0) & ")," & .Address(0, 0) & ",))")
        End With
        For Each pfiPivFldItem In .PivotFields("End Date").PivotItems
            If pfiPivFldItem.Value = "(blank)" Then
                pfiPivFldItem.Visible = False
            Else
                pfiPivFldItem.Visible = (CDate(pfiPivFldItem.Value) = CLng(dtmDate))
            End If
        Next pfiPivFldItem
    End With
   
End Sub

Hi Sam Mathai Chacko,

I just tried your code (mentioned above), but I get a run-time error (1004 -Unable to set the Visible property of the PivotItem class) in the line - pfiPivFldItem.Visible = False. Can you please help me with this?

 

[hudan_vote]

I just tried your code (mentioned above), but I get a run-time error (1004 -Unable to set the Visible property of the PivotItem class) in the line - pfiPivFldItem.Visible = False. Can you please help me with this?

this code is very fantastic but I got the error same thing like VBA learner, I am very need this solving from expert on this group.

the error in the line of
pfiPivFldItem.Visible = (CDate(pfiPivFldItem.Value) = CLng(dtmDate))

Unable to set the Visible property of the PivotItem class