Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Multiple items in a PivotTable filter linked to one cell
Good afternoon all,
I'm currently using the code below to enter a date into a cell (range name Date3) on a sheet which then updates the WE_Date filter on my PivotTable on another sheet. However, I now need to be able to enter a date as usual but want the PivotTable to display the entered date PLUS the previous date (these are week ending dates, so I guess something that reads the cell date then adds another value to the filter which is 'cell date' -7). Any ideas? Current code: Const RegionRangeName As String = "Date3" Const PivotTableName As String = "PivotTable3" Const PivotFieldName As String = "Title_Name" Public Sub UpdatePivotFieldFromRange(RangeName As String, FieldName As String, _ PivotTableName As String) Dim rng As Range Set rng = Application.Range("Date3") Dim pt As PivotTable Dim Sheet As Worksheet For Each Sheet In Application.ActiveWorkbook.Worksheets On Error Resume Next Set pt = Sheet.PivotTables("PivotTable3") Next If pt Is Nothing Then GoTo Ex On Error GoTo Ex pt.ManualUpdate = True Application.EnableEvents = False Application.ScreenUpdating = False Dim Field As PivotField Set Field = pt.PivotFields("WE_Date") Field.ClearAllFilters Field.EnableItemSelection = True SelectPivotItem Field, rng.Text pt.RefreshTable Ex: pt.ManualUpdate = False Application.EnableEvents = True Application.ScreenUpdating = True End Sub Public Sub SelectPivotItem(Field As PivotField, ItemName As Date) Dim Item As PivotItem For Each Item In Field.PivotItems Item.Visible = (Item.Caption = ItemName) Next End Sub Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Not Intersect(Target, Application.Range(RegionRangeName)) _ Is Nothing Then UpdatePivotFieldFromRange _ RegionRangeName, PivotFieldName, PivotTableName End If End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Items in Report Filter of PivotTable not "checked" automatically | New Users to Excel | |||
Filter PivotTable dropdown items to match report filter | Excel Discussion (Misc queries) | |||
PivotTable - show top items | New Users to Excel | |||
Print Pivottable - show all selected items in page filter | Excel Discussion (Misc queries) | |||
PivotTable - calculated items? | Excel Discussion (Misc queries) |