KDavidP1987
Board Regular
- Joined
- Mar 6, 2018
- Messages
- 51
Greetings,
I'm trying to filter between two dates in Excel Pivot Tables using VBA. I need the script to recognize the start date as a date entered by the user in a named cell, and the end date as current date. I've tried several scripts for accomplishing this, but all have failed thus far (various error messages). I don't know if it's simply a problem in the syntax I've attempted, or something else all-together. I just feel like this should be possible.
Apologies for any mess in the code, I've attempted it several ways after scouring online resources. Any help would be greatly appreciated!
Sincerely,
Kristopher Penland
I'm trying to filter between two dates in Excel Pivot Tables using VBA. I need the script to recognize the start date as a date entered by the user in a named cell, and the end date as current date. I've tried several scripts for accomplishing this, but all have failed thus far (various error messages). I don't know if it's simply a problem in the syntax I've attempted, or something else all-together. I just feel like this should be possible.
Code:
[INDENT=2]Sub AdjustPivots2()[/INDENT]
[INDENT=2]'[/INDENT]
[INDENT=2]' AdjustPivots2 Macro[/INDENT]
[INDENT=2]'[/INDENT]
[INDENT=2]
[/INDENT]
[INDENT=2]Dim SDate As String[/INDENT]
[INDENT=2]Dim EDate As String[/INDENT]
[INDENT=2]
[/INDENT]
[INDENT=2]Dim pt As PivotTable[/INDENT]
[INDENT=2]
[/INDENT]
[INDENT=2]'Dim pvtF As PivotField[/INDENT]
[INDENT=2]'Dim pvtI As PivotItem[/INDENT]
[INDENT=2]
[/INDENT]
[INDENT=2]SDate = Format([RStartDate], "Short Date")[/INDENT]
[INDENT=2]EDate = Format(Date, "Short Date")[/INDENT]
[INDENT=2]
[/INDENT]
[INDENT=2]
[/INDENT]
[INDENT=2] Sheets("Pivot_Incidents-Closed").Select[/INDENT]
[INDENT=2] ActiveSheet.PivotTables("PivotTable5").ClearAllFilters[/INDENT]
[INDENT=2] [/INDENT]
[INDENT=2] Sheets("Pivot_Aging Report").Select[/INDENT]
[INDENT=2] ActiveSheet.PivotTables("PivotTable6").ClearAllFilters[/INDENT]
[INDENT=2]
[/INDENT]
[INDENT=2] Sheets("Pivot_Incidents-Closed").Select[/INDENT]
[INDENT=2] ActiveSheet.PivotTables("PivotTable5").PivotFields("Date Closed").CurrentPage _[/INDENT]
[INDENT=2] = "(no data)"[/INDENT]
[INDENT=2] [/INDENT]
[INDENT=2] [/INDENT]
[INDENT=2]'------ Attempted Method # 1 --------[/INDENT]
[INDENT=2]
[/INDENT]
[INDENT=2]'Set pvtF = Worksheets("Pivot_Incidents-Closed").PivotTables("PivotTable5").PivotFields("Date Closed")[/INDENT]
[INDENT=2]'For Each pvtI In pvtF.PivotItems[/INDENT]
[INDENT=2]' If DateValue(pvtI.Name) >= SDate <= EDate Then[/INDENT]
[INDENT=2]' pvtI.Visible = True[/INDENT]
[INDENT=2]' Else[/INDENT]
[INDENT=2]' pvtI.Visible = False[/INDENT]
[INDENT=2]' End If[/INDENT]
[INDENT=2]'Next pvtI[/INDENT]
[INDENT=2] [/INDENT]
[INDENT=2] [/INDENT]
[INDENT=2]'------ Attempted Method # 2 --------[/INDENT]
[INDENT=2] [/INDENT]
[INDENT=2]' With ActiveSheet.PivotTables("PivotTable5").PivotFields("Date Closed").PivotFilters[/INDENT]
[INDENT=2]' .Add Type:=xlDateBetween, Value1:=SDate, Value2:=EDate[/INDENT]
[INDENT=2]' End With[/INDENT]
[INDENT=2] [/INDENT]
[INDENT=2]'------ Attempted Method # 3 --------[/INDENT]
[INDENT=2]
[/INDENT]
[INDENT=2]'Amend here to filter your data[/INDENT]
[INDENT=2]Set pt = Worksheets("Pivot_Incidents-Closed").PivotTables("PivotTable5")[/INDENT]
[INDENT=2] pt.PivotFields("Date Closed").ClearAllFilters[/INDENT]
[INDENT=2] 'This line is needed to clear existing filter before applying new one[/INDENT]
[INDENT=2] pt.PivotFields("Date Closed").PivotFilters.Add Type:=xlDateBetween, _[/INDENT]
[INDENT=2] Value1:=CLng((Range("[RStartDate]").Value)), Value2:=CLng((Now))[/INDENT]
[INDENT=2]
[/INDENT]
[INDENT=2]
[/INDENT]
[INDENT=2]'------ Attempted Method # 4 --------[/INDENT]
[INDENT=2]' Do While SDate <= Date[/INDENT]
[INDENT=2]' On Error GoTo Invalid[/INDENT]
[INDENT=2]'[/INDENT]
[INDENT=2]' With ActiveSheet.PivotTables("PivotTable5").PivotFields("Date Closed")[/INDENT]
[INDENT=2]' .PivotItems(SDate).Visible = True[/INDENT]
[INDENT=2]'[/INDENT]
[INDENT=2]' End With[/INDENT]
[INDENT=2]'[/INDENT]
[INDENT=2]'Invalid:[/INDENT]
[INDENT=2]'[/INDENT]
[INDENT=2]' SDate = SDate + 1[/INDENT]
[INDENT=2]'[/INDENT]
[INDENT=2]' Loop[/INDENT]
[INDENT=2] [/INDENT]
[INDENT=2]
[/INDENT]
[INDENT=2]End Sub[/INDENT]
Apologies for any mess in the code, I've attempted it several ways after scouring online resources. Any help would be greatly appreciated!
Sincerely,
Kristopher Penland