Katterman
Board Regular
- Joined
- May 15, 2014
- Messages
- 103
- Office Version
- 365
- Platform
- Windows
- Mobile
- Web
Hello Everyone
I'm looking for some assistance in Modifying some existing code to filter a pivot table.
I've searched all over the board (and Google) and found many variations of code to do this
but have yet to tweak it to a point that works for me.
I'm using some code provided by Jerry Sullivan found HERE and it works on my pivot table except
for one thing. I'm using an Array to hold a number of dates and i need the code to select only the
dates in the array to be visible. The code as is will select the first and last dates in the array but will also
select ALL dates in between. It does not "ignore" and dates that are midding from the array between the first and last dates.
This is what i have and would like to tweak to use only the vales found in my Array.
This code:
Would need to be replaced with my Array Code:
And as far as the Array goes, it comes from a Dynamic range that populates with dates in a range of either
7 fields (Weekly) or 31 Fields (Monthly). It populates the array based on the Non Empty fields in the range.
Well, Hopefully that made sense and someone can assist.
Like mentioned before, I've looked around a lot and tried many pieces of code
before reaching out for help
Thank you all for reading and to those who reply.
Scott
I'm looking for some assistance in Modifying some existing code to filter a pivot table.
I've searched all over the board (and Google) and found many variations of code to do this
but have yet to tweak it to a point that works for me.
I'm using some code provided by Jerry Sullivan found HERE and it works on my pivot table except
for one thing. I'm using an Array to hold a number of dates and i need the code to select only the
dates in the array to be visible. The code as is will select the first and last dates in the array but will also
select ALL dates in between. It does not "ignore" and dates that are midding from the array between the first and last dates.
This is what i have and would like to tweak to use only the vales found in my Array.
Code:
Sub Pivot_Outage_Dates()
'http://www.mrexcel.com/forum/excel-questions/606328-change-pivot-page-filter-based-upon-two-cell-values-dates.html#post3007354
Dim dtFrom As Date, dtTo As Date
Dim pt As PivotTable
Dim ERow1 As Date
With Sheets("STATS")
ERow1 = Columns("O").Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows, LookIn:=xlValues).Value
End With
With Sheets("NAT_Pivot")
Set pt = .PivotTables("PivotTable6")
dtFrom = Sheets("STATS").Range("O10")
dtTo = ERow1
End With
Call Filter_PivotField_by_Date_Range( _
pt.PivotFields("Date Created"), dtFrom, dtTo)
End Sub
Public Function Filter_PivotField_by_Date_Range(pvtField As PivotField, _
dtFrom As Date, dtTo As Date)
Dim bTemp As Boolean, i As Long
Dim dtTemp As Date, sItem1 As String
On Error Resume Next
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
With pvtField
.Parent.ManualUpdate = True
For i = 1 To .PivotItems.Count
dtTemp = .PivotItems(i)
bTemp = (dtTemp >= dtFrom) And _
(dtTemp <= dtTo)
If bTemp Then
sItem1 = .PivotItems(i)
Exit For
End If
Next i
' If sItem1 = "" Then
' MsgBox "No items are within the specified dates."
' Exit Function
' End If
If .Orientation = xlPageField Then .EnableMultiplePageItems = True
.PivotItems(sItem1).Visible = True
For i = 1 To .PivotItems.Count
dtTemp = .PivotItems(i)
If .PivotItems(i).Visible <> _
((dtTemp >= dtFrom) And (dtTemp <= dtTo)) Then
.PivotItems(i).Visible = Not .PivotItems(i).Visible
End If
Next i
End With
pvtField.Parent.ManualUpdate = False
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Function
Code:
With Sheets("STATS")
ERow1 = Columns("O").Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows, LookIn:=xlValues).Value
End WithWould need to be replaced with my Array Code:
Code:
With Worksheets("STATS")
Arr1 = .Range("O10", .Range("O" & Rows.Count).End(xlUp)).Value
End WithAnd as far as the Array goes, it comes from a Dynamic range that populates with dates in a range of either
7 fields (Weekly) or 31 Fields (Monthly). It populates the array based on the Non Empty fields in the range.
Well, Hopefully that made sense and someone can assist.
Like mentioned before, I've looked around a lot and tried many pieces of code
before reaching out for help
Thank you all for reading and to those who reply.
Scott
