Mark pivotitems of the fields with some conditions

Question

I have few pivotfields and I would like to mark the pivotitems of the fields with some conditions. I have tried using .visible and also have used this application.screenupdating and application.displayfilters. But ist not selecting or deseleting the pivotitems. I would really appreciate each reply.

Filter_PivotField_by_Date_Range. This is a function I am calling in my main just to give my field Name and the Dates I want to compare. After the 2 if conditions I want it to mark (select/Tickmark) the pivotitems in the field and otherwise unmark/ not select the other ones. Make the other one's invisible and then just Exit Sub.

If you guys have any questions I would really help it to make you all clear. Please any comments or code will be appreciated.

    Sub Filter_PivotField_by_Date_Range(pvtField As PivotField, it1 As Date, it2 As Date)

        Dim bTemp As Boolean, i As Long
        Dim dtTemp As Date, dtTemp1 As Date


        dtTemp = Format(CDate(it1), "dd/mm/yyyy")
        dtTemp1 = Format(CDate(it2), "dd/mm/yyyy")
        On Error Resume Next

         With pvtField
          For i = 1 To .PivotItems.Count

           dtFrom = .PivotItems(i)
           If (dtTemp <= dtFrom) Then
               If (dtTemp1 >= dtFrom) Then
               On Error Resume Next
                       Application.ScreenUpdating = False
                       Application.DisplayAlerts = False
                       .PivotItems(i).Visible = True
                       MsgBox (dtFrom)
               Else
                  Application.ScreenUpdating = True
                  Application.DisplayAlerts = True
                   Exit Sub
               End If

            End If
          Next i

        End With

    End Sub

Answer 1

If you have Excel 2010 or later and these PivotTables share the same PivotCache (i.e. they have the same data source) then you can simply set up a slicer, then connect it to the other PivotTables. Then any changes you make in one will automatically be reflected in the others.(Right click on the Slicer and select Report Connections).

In regards to why your code is failing, can you advise what version of Excel you are using, and what the number format of the PivotField is? I suspect the code is failing due to a bug in Excel where VBA fails when recognizing Date variables if you have Excel 2007 or 2010 and are using non US regional date settings and the number format of your PivotField is set to General. (The number format will be stuck on General if there are blanks in your source data. You'll need to get rid of those blanks).

I wrote about this here
and apart from changing the number format of your field, you may also like to try Micheal's approach from the comments if you have Excel 2010.

I see there's also a possible solution at this SO thread.

See the following links for more on this:

Why is this PivotItem.Visible call throwing a TypeMismatch error? https://social.msdn.microsoft.com/Forums/office/en-US/01cb61c7-5e68-4a45-aeff-a70c6dbfe00f/excel-2007-accessing-pivotitemvisible-gives-error-if-the-field-item-value-is-a-date

Answer 2

I tried the below one and its working fine, if possible share you pivot same date.

    Option Explicit
    Sub Macro7()
    Dim StartDate As Date
    Dim EndDate As Date
    Dim pvtDATE As Date
    Dim pvtField As PivotField
    Dim x As PivotItem
    Dim it1
    Dim it2
    Dim y
    it1 = "2/2/2016"
    it2 = "2/20/2016"

    Set pvtField = Excel.Sheets("UrSheet").PivotTables("PivotTable3").PivotFields(2)

    StartDate = Format(CDate(it1), "dd/mm/yyyy")
    EndDate = Format(CDate(it2), "dd/mm/yyyy")


            For Each x In pvtField.PivotItems
            If (x.Value <> "(blank)") Then
             pvtDATE = Format(x.Value, "dd/mm/yyyy")

                If (StartDate <= pvtDATE) Then
                    If (EndDate >= pvtDATE) Then
                        x.Visible = True
                    Else
                        x.Visible = False
                    End If
                Else
                    x.Visible = False
                End If
            End If
            Next

    End Sub

Answer 3

I have tried your code with a small Modification. It worked perfectly for me. Also i had this assumption, your 'it1' is Start Date and 'it2' is End date.

 dtFrom = Format(CDate(.PivotItems(i)), "dd/mm/yyyy")

Storing Date range value to array.

    it1 = "01/01/2013"
    it2 = "01/01/2014"
    Dim dtTemp1 As Date
    Dim dtTemp2 As Date
    Dim dateArray()
    dtTemp1 = Format(CDate(it1), "dd/mm/yyyy")
    dtTemp2 = Format(CDate(it2), "dd/mm/yyyy")
    NoOfDays = DateDiff("D", dtTemp1, dtTemp2)
    ReDim dateArray(NoOfDays)

    For i = 0 To NoOfDays
    dateArray(i) = dtTemp1 + i
    Next