Hi there! First time posting here, you all have been amazing help while building out multiple projects. Really appreciate the community.
I currently have two Pivot tables [PivotTable1 and PivotTable2] on a sheet("PivotTable"). I Have two Filters for each, one is Auditor, the other is Date.
Question 1: I am trying to limit the selection of the Date Filter in PivotTable1 to only allow people to select one date, not multiple. Is this something that can be done?
Question 2: I have a Name_Range called PivotFilterDate which takes the selected date in the PivotTable1 Date Filter (ie:8/12/2015) and automatically lists out the dates in that selected month in a column (ie:8/1/2015,8/2/2015,8/3/2015,etc.). I would like to AutoFilter with VBA PivotTable2's Date Filter with those dates in that Name_Range.
Here is the code I am currently using
----------------------------------------------
Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim rang As Range
Dim rangle As Range
Set rng = Me.Range("AuditorFilter")
Set rang = Me.Range("PivotFilterDate")
Set rangle = Me.Range("datefilter")
If Not Application.Intersect(Target, rng) Is Nothing Then
With Sheets("PivotTable").PivotTables("PivotTable1").PivotFields("Auditor")
.ClearAllFilters
If Len(rng.Value) > 0 Then .CurrentPage = rng.Value
End With
End If
If Not Application.Intersect(Target, rng) Is Nothing Then
With Sheets("PivotTable").PivotTables("PivotTable2").PivotFields("Auditor")
.ClearAllFilters
If Len(rng.Value) > 0 Then .CurrentPage = rng.Value
End With
End If
If Not Application.Intersect(Target, rangle) Is Nothing Then
With Sheets("PivotTable").PivotTables("PivotTable2").PivotFields("Date")
.ClearAllFilters
If Len(rangle.Value) > 0 Then .CurrentPage = rang.Value
End With
End If
If MsgBox("Make sure to Click the button Update Daily/Weekly Lists before printing a PDF", vbOKOnly) = vbOK Then
Else: End If
End Sub
--------------------------------------------------------
(FYI: This Code also refers to another named range that updates the two pivot tables with a selected Auditor from a drop down list. This works magically)
Unfortunately it keeps selecting the first date in the list (which in this case is 8/1/2015), not any of the others in the range. It appears that the code only allows one item to pass thru the filter, not a list.
Please help? Thanks in advance...
I currently have two Pivot tables [PivotTable1 and PivotTable2] on a sheet("PivotTable"). I Have two Filters for each, one is Auditor, the other is Date.
Question 1: I am trying to limit the selection of the Date Filter in PivotTable1 to only allow people to select one date, not multiple. Is this something that can be done?
Question 2: I have a Name_Range called PivotFilterDate which takes the selected date in the PivotTable1 Date Filter (ie:8/12/2015) and automatically lists out the dates in that selected month in a column (ie:8/1/2015,8/2/2015,8/3/2015,etc.). I would like to AutoFilter with VBA PivotTable2's Date Filter with those dates in that Name_Range.
Here is the code I am currently using
----------------------------------------------
Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim rang As Range
Dim rangle As Range
Set rng = Me.Range("AuditorFilter")
Set rang = Me.Range("PivotFilterDate")
Set rangle = Me.Range("datefilter")
If Not Application.Intersect(Target, rng) Is Nothing Then
With Sheets("PivotTable").PivotTables("PivotTable1").PivotFields("Auditor")
.ClearAllFilters
If Len(rng.Value) > 0 Then .CurrentPage = rng.Value
End With
End If
If Not Application.Intersect(Target, rng) Is Nothing Then
With Sheets("PivotTable").PivotTables("PivotTable2").PivotFields("Auditor")
.ClearAllFilters
If Len(rng.Value) > 0 Then .CurrentPage = rng.Value
End With
End If
If Not Application.Intersect(Target, rangle) Is Nothing Then
With Sheets("PivotTable").PivotTables("PivotTable2").PivotFields("Date")
.ClearAllFilters
If Len(rangle.Value) > 0 Then .CurrentPage = rang.Value
End With
End If
If MsgBox("Make sure to Click the button Update Daily/Weekly Lists before printing a PDF", vbOKOnly) = vbOK Then
Else: End If
End Sub
--------------------------------------------------------
(FYI: This Code also refers to another named range that updates the two pivot tables with a selected Auditor from a drop down list. This works magically)
Unfortunately it keeps selecting the first date in the list (which in this case is 8/1/2015), not any of the others in the range. It appears that the code only allows one item to pass thru the filter, not a list.
Please help? Thanks in advance...
