Why would changing the start and end date of a Slicercache.TimelineState.SetFilterDateRange result in a '1004' Application or object defined error?

Question

I'm trying to fix a 1004 error in the following code. Also, please note that I have removed the non-essential stuff like my own comments/debugs and the rest of the code after the error. But I have left in the comments from the guy who wrote the script.

Background: What this code is supposed to do is reset a time period filter for some pivottables that retrieve accounting data to do Percentage Of Completion calculations. However, since a few weeks we have been getting a 1004 error and the code does not run anymore.

Attempts so far: So what have I tried/looked at to fix this? I have tried passing different start and end date values to see if this could fix it but that did not help. I have also examined the reports that the cache has been connected to but this didn't help me get an understanding of the problem either. Because none of the pivot tables or queries are showing any errors. The rest of my efforts are best summed up as reading up on all things pivot table and checking forums for similar problems.

Thanks in advance for your help.

' This is a subroutine to adjust the selections in the timeline slicers for the pivot data.

    Dim startDate As Date
    Dim endDate As Date
    Dim cache As SlicerCache

' Startdate for all slicers is the min cal date
    startDate = ActiveWorkbook.Sheets("Helpers").Range("MinCalDate").Value

' Set the end date to the last date of this period
    If ActiveWorkbook.Sheets("Helpers").Range("PeriodStart").Value > Date Then
        endDate = DateSerial(Year(Date), Month(Date) + 1, 0)
    Else
        endDate = ActiveWorkbook.Sheets("Helpers").Range("PeriodEnd").Value     
'Set slicer 1 End date as slicer selection instead
    End If

' Transaction dates are between the earlies start date and end date
    Set cache = ActiveWorkbook.SlicerCaches("Timeline_TRANSACTIONDATE")
    
    ActiveWorkbook.SlicerCaches("Timeline_TRANSACTIONDATE").TimelineState.SetFilterDateRange startDate, endDate

.....

End Sub

Answer 1

There are 2 kinds of slicers. xlslicer and xltimeline. If you use .TimelineState.SetFilterDateRange on xlslicer then you will get an error.

You will have to use an xltimeline slicer for your purpose and then your code will work.

Here is an example

Option Explicit

Sub Sample()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim SlcrCache As SlicerCache
    Dim SlcrName As String
    Dim sDate As Date
    Dim eDate As Date
    
    Set wb = ThisWorkbook
    Set ws = wb.Sheets("Sheet1")
    
    '~~> Start Date End Date
    sDate = DateValue(ws.Range("B1").Value)
    eDate = DateValue(ws.Range("B2").Value)
    
    '~~> Name of your slicer
    SlcrName = "NativeTimeline_Date"
    
    Set SlcrCache = wb.SlicerCaches(SlcrName)
    
    SlcrCache.TimelineState.SetFilterDateRange sDate, eDate
End Sub