Timeline Slicer Select Date Range using a variable

Question

I have a sheet that I would like to update the timeline date range using a start and end date variable. Information is periodically filled in on a daily basis, so I find the latest date of data entry and save it as the end date. Then I want to show the rolling month up to that date.

Ex. Last day of data entry is June 17th (17/06/2015), that gets assigned to endDate. The startDate goes back a month and is May 18th (18/05/2015).

This code is the recorded macro for an arbitrary selected time range.

ActiveSheet.Shapes.Range(Array("Timeline 1")).Select
ActiveWorkbook.SlicerCaches("NativeTimeline_Timeline1").TimelineState. _
    SetFilterDateRange "12/05/2015", "16/06/2015"

When I put my startDate and endDate variables in it doesn't work.

Dim startDate, endDate As Date

ActiveSheet.Shapes.Range(Array("Timeline 1")).Select
ActiveWorkbook.SlicerCaches("NativeTimeline_Timeline1").TimelineState. _
    SetFilterDateRange startDate, endDate

Answer 1

You need to assign values to startDateand endDate, unless you are passing in arguments to your procedure, but I don't see that from this code.

Assuming this procedure doesn't take arguments, then if your start date and end date reside in cells, say "A1" and "A100", respectively, you could assign them in your code.

startDate = Range("A1")
endDate = Range("A100")

Now you will be able to use those variables in your SetFilterDateRange method.

Also it is good practice to assign a data type to each variable. Right now, startDate is a Variant. So in a single-line declaration of multiple variables, you should write

Dim startDate As Date, endDate As Date

Answer 2

You can use this kind of grammer:

ActiveWorkbook.SlicerCaches("NativeTimeline_Timeline1").TimelineState. _
SetFilterDateRange year(startDate) & "/" & month(startdate) & "/" & 
day(startdate), year(endDate) & "/" & month(enddate) & "/" & day(enddate)

it's OK!