Selecting date range in a slicer using macro

[Cohen]

I would like to seek help regarding writing a macro that would automatically select slicer date range from a cell value from a different workbook.. I have 7-days date range that change every week.. Start date is on other workbook cell a1 and end date on a7. Hope you can help me

 

[Jerry Sullivan]

Welcome to MrExcel,

If your PivotTable has an OLAP data source, as is the case with Pivots created with PowerPivot, then it will be helpful to see the structured field names and syntax for the date reference.

Use the Macro Recorder to record what happens when you manually filter the dates. If you'll post that code here, I'll try to help.

 

[Cohen]

Hi Jerry,

Thanks for your prompt reply. I have tried something like below: but i'm getting error - run time error '1004: "application-defined or object-defined error"

FilterValue = ActiveSheet.Range("ah50").Value 'get value for filter


ActiveSheet.PivotTables("PivotTable2") _
.PivotFields("[Tbl_POS_OD_RBD_Bkgs].[BKG_DT].[BKG_DT]").ClearAllFilters
ActiveSheet.PivotTables("PivotTable2").PivotFields("[Tbl_POS_OD_RBD_Bkgs].[BKG_DT].[BKG_DT]") _
.CurrentPageName = "[Tbl_POS_OD_RBD_Bkgs].[BKG_DT].&[" & FilterValue & "]"

Cell AH50-56 has dates from 11/02/2016 (dd-mm-yy) format.

when i recorded a macro to select ppfilter it gave me below codes:

ActiveSheet.PivotTables("PivotTable2").PivotFields( _
"[Tbl_POS_OD_RBD_Bkgs].[BKG_DT].[BKG_DT]").VisibleItemsList = Array( _
"[Tbl_POS_OD_RBD_Bkgs].[BKG_DT].&[2016-02-11T00:00:00]", _
"[Tbl_POS_OD_RBD_Bkgs].[BKG_DT].&[2016-02-12T00:00:00]", _
"[Tbl_POS_OD_RBD_Bkgs].[BKG_DT].&[2016-02-13T00:00:00]", _
"[Tbl_POS_OD_RBD_Bkgs].[BKG_DT].&[2016-02-14T00:00:00]", _
"[Tbl_POS_OD_RBD_Bkgs].[BKG_DT].&[2016-02-15T00:00:00]", _
"[Tbl_POS_OD_RBD_Bkgs].[BKG_DT].&[2016-02-16T00:00:00]", _
"[Tbl_POS_OD_RBD_Bkgs].[BKG_DT].&[2016-02-17T00:00:00]")

I was added ppfilters coz i'm unable to control the slicers using vba to select a date range from 11/02/2016 - 17/02/2016.

I wanted date selection to be triggered by the cell values of AH50 and AH56.

I also wanted to control the slicer for the travel month: "[Tbl_POS_OD_RBD_Bkgs].[TRVL_MNTH].[TRVL_MNTH]" by a cell value in AH49.

Hope you can help me again.

 

[Jerry Sullivan]

Hi Cohen, Please clarify a few things so we have a better chance of this working without a lot of back and forth:

1. What is the file name of the Workbook that has the cells with the dates?
2. What is the name of the Worksheet that holds the dates in AH50:AH56?

3. What is the file name of the Workbook that has the PivotTable(s)?
4. What is the name of the Worksheet that holds the PivotTable(s)?

I wanted date selection to be triggered by the cell values of AH50 and AH56.

I also wanted to control the slicer for the travel month: "[Tbl_POS_OD_RBD_Bkgs].[TRVL_MNTH].[TRVL_MNTH]" by a cell value in AH49.

If you want this to be triggered automatically, I'd suggest you do that on any change to AH50. The other cells AH51:AH56 aren't needed since those can be inferred from AH50.

5. Will the workbook with the PivotTables always be open when AH50 is changed? (if not the code will need to display an error message or open the file).

6. Does the user do a direct entry into AH50 to change the date, or does AH50 contain a formula?

I also wanted to control the slicer for the travel month: "[Tbl_POS_OD_RBD_Bkgs].[TRVL_MNTH].[TRVL_MNTH]" by a cell value in AH49.

Let's get the BKG_DT macro working first.
Then the TRVL_MNTH should be a relatively simple variation.

 

[Cohen]

Hi Jerry,

Please find below details:

1. What is the file name of the Workbook that has the cells with the dates? WFS.xlsx
2. What is the name of the Worksheet that holds the dates in AH50:AH56? Calculations

3. What is the file name of the Workbook that has the PivotTable(s)? POSODRBD.xlsm
4. What is the name of the Worksheet that holds the PivotTable(s)? TOP 30 Dstn5. Will the workbook with the PivotTables always be open when AH50 is changed? (if not the code will need to display an error message or open the file). Yup it will be open

6. Does the user do a direct entry into AH50 to change the date, or does AH50 contain a formula? It's formula and will change automatically. Let's use cell B10 (start date) + 6days (end date).

Thanks!

 

[Jerry Sullivan]

Hi Cohen, Here's a general purpose function that can be used to filter a PivotTable with an OLAP data source to show and array of items. For your objective, we'll pass and array with the 7 dates.

Paste this into a Standard Code Module (like Module1) in your workbook POSODRBD.xlsm...

Private Function sOLAP_FilterByItemList(ByVal pvf As PivotField, _
   ByVal vItemsToBeVisible As Variant, _
   ByVal sItemPattern As String) As String

'--filters an OLAP pivotTable to display a list of items,
'    where some of the items might not exist
'--works by testing whether each pivotitem exists, then building an
'    array of existing items to be used with the VisibleItemsList property
'--requires Excel 2007 or later

'--Input Parameters:
'  pvf                pivotfield object to be filtered
'  vItemsToBeVisible  1D array of strings representing items to be visible
'  sItemPattern       string that has MDX pattern of pivotItem reference
'                     where the text "ThisItem" will be replaced by each
'                     item in vItemsToBeVisible to make pivotItem references.
'                     e.g.: "[tblSales].[product_name].&[ThisItem]"
   
 Dim lFilterItemCount As Long, lNdx As Long
 Dim vFilterArray As Variant
 Dim vSaveVisibleItemsList As Variant
 Dim sReturnMsg As String, sPivotItemName As String
 
 '--store existing visible items
 vSaveVisibleItemsList = pvf.VisibleItemsList
 
 If Not (IsArray(vItemsToBeVisible)) Then _
   vItemsToBeVisible = Array(vItemsToBeVisible)
 ReDim vFilterArray(1 To _
   UBound(vItemsToBeVisible) - LBound(vItemsToBeVisible) + 1)
 pvf.Parent.ManualUpdate = True
 
 '--check if pivotitem exists then build array of items that exist
 For lNdx = LBound(vItemsToBeVisible) To UBound(vItemsToBeVisible)
   '--create MDX format pivotItem reference by substituting item into pattern
   sPivotItemName = Replace(sItemPattern, "ThisItem", vItemsToBeVisible(lNdx))
   
   '--attempt to make specified item the only visible item
   On Error Resume Next
   pvf.VisibleItemsList = Array(sPivotItemName)
   On Error GoTo 0
   
   '--if item doesn't exist in field, this will be false
   If LCase$(sPivotItemName) = LCase$(pvf.VisibleItemsList(1)) Then
      lFilterItemCount = lFilterItemCount + 1
      vFilterArray(lFilterItemCount) = sPivotItemName
   End If
 Next lNdx
 
 '--if at least one existing item found, filter pivot using array
 If lFilterItemCount > 0 Then
   ReDim Preserve vFilterArray(1 To lFilterItemCount)
   pvf.VisibleItemsList = vFilterArray
 Else
   sReturnMsg = "No matching items found."
   pvf.VisibleItemsList = vSaveVisibleItemsList
 End If
 pvf.Parent.ManualUpdate = False

 sOLAP_FilterByItemList = sReturnMsg
End Function

Next, paste this Sub that calls the function into the same Standard Code Module.

Sub FilterPivotForWeek()
 Dim dtStart As Date
 Dim lDay As Long
 Dim pvt As PivotTable
 Dim sErrMsg As String, sTemplate As String
 Dim vItemsToBeVisible As Variant

 On Error GoTo ErrProc
 With Application
   .EnableCancelKey = xlErrorHandler
   .ScreenUpdating = False
   .DisplayStatusBar = False
   .EnableEvents = False
 End With
  
 On Error Resume Next
 dtStart = Workbooks("WFS.xlsx").Sheets("Calculations").Range("B10").Value
 On Error GoTo 0
 
 If dtStart = 0 Then
   MsgBox "Error reading start date."
 Else
    '--create array of filter items for week beginning at dtStart
    ReDim vItemsToBeVisible(0 To 6)
    For lDay = 0 To 6
      vItemsToBeVisible(lDay) = Format(dtStart + lDay, "YYYY-MM-DD")
      Debug.Print Format(dtStart + lDay, "YYYY-MM-DD")
    Next lDay
   
    Set pvt = ThisWorkbook.Sheets("TOP 30 Dstn5").PivotTables(1)
    '--call function
    sErrMsg = sOLAP_FilterByItemList( _
      pvf:=pvt.PivotFields("[Tbl_POS_OD_RBD_Bkgs].[BKG_DT].[BKG_DT]"), _
      vItemsToBeVisible:=vItemsToBeVisible, _
      sItemPattern:="[Tbl_POS_OD_RBD_Bkgs].[BKG_DT].&[ThisItemT00:00:00]")
 End If
ExitProc:
 On Error Resume Next
 With Application
   .EnableEvents = True
   .DisplayStatusBar = True
   .ScreenUpdating = True
 End With
 If Len(sErrMsg) > 0 Then MsgBox sErrMsg
 Exit Sub
 
ErrProc:
 sErrMsg = Err.Number & " - " & Err.Description
 Resume ExitProc
End Sub

Test the code by running the macro "FilterPivotForWeek". This is untested since I can't easily replicate your setup.

If it works, then I'll suggest some code to automatically trigger running "FilterPivotForWeek". Since your workbook with the dates is not one that supports macros, and it's unclear to me whether B10 is being changed by user input, a simple approach would be to check the Start Date any time sheet "TOP 30 Dstn5" is activated and filter the pivot if that date has been changed.

 

[Cohen]

I have tried to run the "FilterPivotForWeek" and got a debug : Set pvt = ThisWorkbook.Sheets("TOP 30 Dstn").PivotTables(1)
Pivot table name is "PivotTable2". Shall i just change it to PivotTable(2)?

 

[Cohen]

I have another workbook that has macros where i'll save the macros.

 

[Cohen]

Hi Jerry,

It worked like a charm!!!! thank you very much!!!!! i figured what needed to be altered. it was my fault not being clear with you from the beginning. i changed the "ThisWorkbook" to Workbooks("POSODRBD.xlsm")... and changed "TOP 30 Dstn5" to "TOP 30 Dstn". and just added a macro to add "BKG_DT" pivot filter, coz i realized that i'm getting the debug message coz of all those three things. YOU GOT it all right from my initial info, it's just that i wasn't crystal clear with my info. Thank you very much Jerry really appreciate it. Hope you can still help me in the future if i got stuck with something again.

Thanks a million!

 

[jdmiranda]

Hello Jerry,
I came across this thread when looking for a way to selecting a date range using a macro. I tested the code you provided here, and works excellent, but what I need is a date range like dtStart to dtEnd for example. More specifically, a two date ranges, like Before start to Before End, and After Start to After End. So I can compare two different time periods. Can this code be modified to be able to do that? If so, can you share your thoughts? Thank you so much!