DoodlesMama
New Member
- Joined
- Aug 26, 2012
- Messages
- 14
Hello,
I am creating the report that is updated daily and need assistance with dates in Pivot Tables within the report.
I have one sheet within the report that is for service call aging and contains two pivot tables on it for one and seven days. Our reporting system runs one day behind, so the formula needs to actually be for 2 and 8 days.
Sub Aging_Calls()
Dim OneDay As String
OneDay = Format(Date - 2, "mm/dd/yyyy")
SevenDay = Format(Date - 8, "mm/dd/yyyy")
Sheets("Aging Calls").Select
'OLDER THAN ONE DAY
ActiveSheet.PivotTables("DBD4_Calls_Over_1Day").PivotFields("CONTACT DATE").CurrentPage = "(All)"
ActiveSheet.PivotTables("DBD4_Calls_Over_1Day").PivotFields("CONTACT DATE").PivotItems(OneDay).Visible = False
'OLDER THAN ONE WEEK
ActiveSheet.PivotTables("DBD4_Calls_Over_1Week").PivotFields("CONTACT DATE").CurrentPage = "(All)"
ActiveSheet.PivotTables("DBD4_Calls_Over_1Week").PivotFields("CONTACT DATE").PivotItems(SevenDay).Visible = False
End Sub
Can any of you please help me?
I am creating the report that is updated daily and need assistance with dates in Pivot Tables within the report.
I have one sheet within the report that is for service call aging and contains two pivot tables on it for one and seven days. Our reporting system runs one day behind, so the formula needs to actually be for 2 and 8 days.
- One for any calls over 1 day. =Today()-2
- One for any calls over 7 days. =Today()-7
- ERROR: Run-time error '1004: Unable to get the PivotItems property of the PivotField class.
- VBA:
Sub Aging_Calls()
Dim OneDay As String
OneDay = Format(Date - 2, "mm/dd/yyyy")
SevenDay = Format(Date - 8, "mm/dd/yyyy")
Sheets("Aging Calls").Select
'OLDER THAN ONE DAY
ActiveSheet.PivotTables("DBD4_Calls_Over_1Day").PivotFields("CONTACT DATE").CurrentPage = "(All)"
ActiveSheet.PivotTables("DBD4_Calls_Over_1Day").PivotFields("CONTACT DATE").PivotItems(OneDay).Visible = False
'OLDER THAN ONE WEEK
ActiveSheet.PivotTables("DBD4_Calls_Over_1Week").PivotFields("CONTACT DATE").CurrentPage = "(All)"
ActiveSheet.PivotTables("DBD4_Calls_Over_1Week").PivotFields("CONTACT DATE").PivotItems(SevenDay).Visible = False
End Sub
Can any of you please help me?