I'm hoping someone can help. I have a dashboard created by someone else where there are numerous tables across sheets all operating from drop down date selections (From - To) on sheet 1. I have been asked to add to this, and have created pivot tables that are most suitable to the work. The problem I have is that I need them to filter based on the drop down dates on sheet 1.
I am hoping this is possible via VBA.
I have been able to get my pivot reports to filter based on another drop down which is text based. But cannot get the same code (when tweaked to focus on the "month" option and associated drop down cell) to work for a date selection, and I also cannot figure out how to allow multiple selections so that I can choose the date range.
The code I have been using is as follows:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
Dim pt As PivotTable
Dim pi As PivotItem
Dim strField As String
strField = "Region"
On Error Resume Next
Application.EnableEvents = False
Application.ScreenUpdating = False
If Target.Address = Range("D2").Address Then
For Each ws In ThisWorkbook.Worksheets
For Each pt In ws.PivotTables
With pt.PageFields(strField)
For Each pi In .PivotItems
If pi.Value = Target.Value Then
.CurrentPage = Target.Value
Exit For
Else
.CurrentPage = "(All)"
End If
Next pi
End With
Next pt
Next ws
End If
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
Any help anyone is able to provide would be much appreciated. I'm fairly new to VBA and trying my best to tweak code I am finding online but struggling.
Thanks
Revised: I also tried the below code that I found elsewhere which was being used for selecting date ranges
Sub FilterPivotDates()
'
Dim dStart As Date
Dim dEnd As Date
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Application.ScreenUpdating = False
On Error Resume Next
dStart = Sheets("Pivots").Range("F2").Value
dEnd = Sheets("Pivots").Range("f3").Value
Set pt = ActiveSheet.PivotTable1
Set pf = pt.PivotFields("Month")
pt.ManualUpdate = True
pf.EnableMultiplePageItems = True
For Each pi In pf.PivotItems
pi.Visible = True
Next pi
For Each pi In pf.PivotItems
If pi.Value < dStart Or pi.Value > dEnd Then
pi.Visible = False
End If
Next pi
Application.ScreenUpdating = False
pt.ManualUpdate = False
Set pf = Nothing
Set pt = Nothing
End Sub
In the example I found this was being operated by a button but I just tried it in the sheet. But this also does not work for me.
dd/mm/yyyy
or something similar ? – Shai Rado