Filter Pivot Items that start with a date today

[fairymemay (noona)]

I currently have a situation where i have a list of pivotitems that my macro has recorded
But the problem is the code bombs out when new or different pivotitems.

I want filter that start date today

Now I have code as below

Sub Macro7()

    ActiveSheet.PivotTables("PivotTable27").PivotFields("Report_DATE").CurrentPage _
        = "(All)"
    With ActiveSheet.PivotTables("PivotTable27").PivotFields("Report_DATE")
        .PivotItems("01/05/2024 9:11").Visible = True
        .PivotItems("01/05/2024 10:01").Visible = True
        .PivotItems("01/05/2024 15:01").Visible = True
    End With
End Sub

Please guide me for solve it. (solution by macro,vba)

Thank you.

[Parvathy (PS Parvathy)]

Hi @fairymemay

Could you share the sample excel.
and you can try this macros code:

Sub FilterPivotTableByCurrentDate()
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim pi As PivotItem
    Dim currentDate As Date
    Dim itemDate As Date
    
    ' Set the PivotTable reference
    Set pt = ActiveSheet.PivotTables("PivotTable27")
    
    ' Set the PivotField reference
    Set pf = pt.PivotFields("Report_DATE")
    
    ' Set the current date
    currentDate = Date
    
    ' Iterate through each PivotItem in the PivotField
    For Each pi In pf.PivotItems
        ' Extract the date from the PivotItem name (assuming the date is at the beginning of the name)
        itemDate = DateValue(Mid(pi.Name, 1, 10))
        
        ' Make the PivotItem visible only if its date matches the current date
        pi.Visible = (itemDate = currentDate)
    Next pi
End Sub

Hope it helps!!
Regards

[fairymemay (noona)]

@Parvathy
error type mismatch

excel file as attached. (remove sensitive data already)
input.xlsx (3.8 MB)

I want to filter CREATE_DATE in sheet Pivot row 168

Please guide me for solve it.

[Parvathy (PS Parvathy)]

HI @fairymemay

Happy to help if you Could you explain your requirement again.

Regards

[fairymemay (noona)]

@Parvathy I want to filter CREATE_Date and PREFER_APPNT_DATE = today (but in file don’t have data today —> you can filter yesterday instead )

[Parvathy (PS Parvathy)]

Hi @fairymemay

I think this VBA code should work for you.

Sub FilterPivotTableByYesterdayDate()
    Dim wsData As Worksheet
    Dim wsPivot As Worksheet
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim pi As PivotItem
    Dim yesterdayDate As Date
    Dim itemDate As Date
    Dim datePart As String
    
    ' Set the references to the DataSheet and PivotSheet
    Set wsData = ThisWorkbook.Sheets("Data") ' Adjust to your actual sheet name
    Set wsPivot = ThisWorkbook.Sheets("Pivot") ' Adjust to your actual sheet name
    
    ' Set the PivotTable reference
    Set pt = wsPivot.PivotTables("PivotTable27") ' Adjust to your actual PivotTable name
    
    ' Set the PivotField reference
    Set pf = pt.PivotFields("CREATE_DATE") ' Adjust to your actual PivotField name
    
    ' Calculate yesterday's date
    yesterdayDate = Date - 1
    
    ' Clear any existing filters
    pf.ClearAllFilters
    
    ' Iterate through each PivotItem in the PivotField
    For Each pi In pf.PivotItems
        ' Extract the date part from the PivotItem name (assuming the date is at the beginning of the name)
        datePart = Mid(pi.Name, 1, 10)
        
        ' Attempt to convert the date part to a Date type
        If IsDate(datePart) Then
            itemDate = CDate(datePart)
            
            ' Make the PivotItem visible only if its date matches yesterday's date
            If DateValue(itemDate) = yesterdayDate Then
                pi.Visible = True
            Else
                pi.Visible = False
            End If
        End If
    Next pi
End Sub

Happy to help if any difficulties faced.

Regards

[fairymemay (noona)]

@Parvathy I have last question.
My computer date format = dd-mmm-yy
How to change format in VBA to mm/dd/yyyy?

[Parvathy (PS Parvathy)]

Hi @fairymemay

Do you want to this for this for an Excel Sheet. Specify.

Regards

[fairymemay (noona)]

@Parvathy After with your code.
I found some record datePart are wrong beause date or month have 1 char.

Can you suggest for modify code?

Thank you

[Parvathy (PS Parvathy)]

Hi @fairymemay

Can you try this code:

Sub FilterPivotTableByYesterdayDate()
    Dim wsData As Worksheet
    Dim wsPivot As Worksheet
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim pi As PivotItem
    Dim yesterdayDate As Date
    Dim itemDate As Date
    Dim datePart As String
    
    ' Set the references to the DataSheet and PivotSheet
    Set wsData = ThisWorkbook.Sheets("Data") ' Adjust to your actual sheet name
    Set wsPivot = ThisWorkbook.Sheets("Pivot") ' Adjust to your actual sheet name
    
    ' Set the PivotTable reference
    Set pt = wsPivot.PivotTables("PivotTable27") ' Adjust to your actual PivotTable name
    
    ' Set the PivotField reference
    Set pf = pt.PivotFields("CREATE_DATE") ' Adjust to your actual PivotField name
    
    ' Calculate yesterday's date
    yesterdayDate = Date - 1
    
    ' Clear any existing filters
    pf.ClearAllFilters
    
    ' Iterate through each PivotItem in the PivotField
    For Each pi In pf.PivotItems
        ' Extract the date part from the PivotItem name (assuming the date is at the beginning of the name)
        datePart = Mid(pi.Name, 1, 10)
        
        ' Attempt to convert the date part to a Date type
        If IsDate(datePart) Then
            itemDate = CDate(datePart)
            
            ' Ensure that the date is in "MM/dd/yyyy" format
            formattedDate = Format(itemDate, "MM/dd/yyyy")
            
            ' Make the PivotItem visible only if its formatted date matches yesterday's date
            If DateValue(formattedDate) = yesterdayDate Then
                pi.Visible = True
            Else
                pi.Visible = False
            End If
        End If
    Next pi
End Sub

Regards

[fairymemay (noona)]

@Parvathy I use yesterday-3
But output wrong.

[Parvathy (PS Parvathy)]

Hi @fairymemay

Try this code once

Sub FilterPivotTableByYesterdayDate()
    Dim wsData As Worksheet
    Dim wsPivot As Worksheet
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim pi As PivotItem
    Dim yesterdayDate As Date
    Dim itemDate As Date
    Dim datePart As String
    
    ' Set the references to the DataSheet and PivotSheet
    Set wsData = ThisWorkbook.Sheets("Data") ' Adjust to your actual sheet name
    Set wsPivot = ThisWorkbook.Sheets("Pivot") ' Adjust to your actual sheet name
    
    ' Set the PivotTable reference
    Set pt = wsPivot.PivotTables("PivotTable27") ' Adjust to your actual PivotTable name
    
    ' Set the PivotField reference
    Set pf = pt.PivotFields("CREATE_DATE") ' Adjust to your actual PivotField name
    
    ' Calculate yesterday's date
    yesterdayDate = Date - 3
    
    ' Clear any existing filters
    pf.ClearAllFilters
    
    ' Iterate through each PivotItem in the PivotField
    For Each pi In pf.PivotItems
        ' Extract the date part from the PivotItem name (assuming the date is at the beginning of the name)
        datePart = Mid(pi.Name, 1, 10)
        
        ' Attempt to convert the date part to a Date type (assuming the original format is "dd-MM-yy HH:mm")
        If IsDate(datePart) Then
            itemDate = CDate(datePart)
            
            ' Convert the date to the desired format "MM/dd/yyyy"
            formattedDate = Format(itemDate, "MM/dd/yyyy")
            
            ' Make the PivotItem visible only if its date matches yesterday's date
            If DateValue(formattedDate) = yesterdayDate Then
                pi.Visible = True
            Else
                pi.Visible = False
            End If
        End If
    Next pi
End Sub

Regards

[fairymemay (noona)]

@Parvathy I try with debug in condition true.

But result blank.

[fairymemay (noona)]

@Parvathy I try debug for compare data.
But don’t found date in year 2024

[Parvathy (PS Parvathy)]

Hi @fairymemay

What is the output you require. Can you please specify.

Regards

[fairymemay (noona)]

@Parvathy If use date = today-3 → filter in create_date = choose 4/1/2024 only as below.

[Parvathy (PS Parvathy)]

Hi @fairymemay

Can you try this code:

Sub FilterPivotTableByDate()
    Dim wsData As Worksheet
    Dim wsPivot As Worksheet
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim pi As PivotItem
    Dim targetDate As Date
    Dim itemDate As Date
    Dim datePart As String
    
    ' Set the references to the DataSheet and PivotSheet
    Set wsData = ThisWorkbook.Sheets("Data") ' Adjust to your actual sheet name
    Set wsPivot = ThisWorkbook.Sheets("Pivot") ' Adjust to your actual sheet name
    
    ' Set the PivotTable reference
    Set pt = wsPivot.PivotTables("PivotTable27") ' Adjust to your actual PivotTable name
    
    ' Set the PivotField reference
    Set pf = pt.PivotFields("CREATE_DATE") ' Adjust to your actual PivotField name
    
    ' Calculate the target date (today minus 3 days)
    targetDate = Date - 3
    
    ' Clear any existing filters
    pf.ClearAllFilters
    
    ' Iterate through each PivotItem in the PivotField
    For Each pi In pf.PivotItems
        ' Extract the date part from the PivotItem name (assuming the date is at the beginning of the name)
        datePart = Mid(pi.Name, 1, 10)
        
        ' Attempt to convert the date part to a Date type (assuming the original format is "dd-MM-yy HH:mm")
        If IsDate(datePart) Then
            itemDate = CDate(datePart)
              ' Convert the date to the desired format "MM/dd/yyyy"
            formattedDate = Format(itemDate, "MM/dd/yyyy")
            
            ' Make the PivotItem visible only if its date matches yesterday's date
            If DateValue(formattedDate) = yesterdayDate Then
                pi.Visible = True
            Else
                pi.Visible = False
            End If
        End If
    Next pi
End Sub

Happy to help again if not sorted.

Regards

[fairymemay (noona)]

@Parvathy thank you.
It correct.

[Parvathy (PS Parvathy)]

You’re welcome, @fairymemay. Glad to have helped.

Happy Automation

Regards

[fairymemay (noona)]

@Parvathy I run your code again but output wrong.
If not filter 04-018-24 only.

input.xlsx (3.8 MB)