Hello,
First post on the forums. Looking forward to engaging with all of you.
I am trying to write a VBA procedure that loops through all of the pivot fields in a pivot table and adds the fields as data field values based on a few conditions. First, if the pivot field is not a date data type, then do not add it. 2nd if it is a date, evaluate if it is a date within 56 days (8 weeks) from today's date/system date. If that condition is true then add the value as a data field to the pivot table. Here is what I have come up with, but it is not working and I am not exactly sure why:
Any help you can provide is greatly appreciated. Thanks!
First post on the forums. Looking forward to engaging with all of you.
I am trying to write a VBA procedure that loops through all of the pivot fields in a pivot table and adds the fields as data field values based on a few conditions. First, if the pivot field is not a date data type, then do not add it. 2nd if it is a date, evaluate if it is a date within 56 days (8 weeks) from today's date/system date. If that condition is true then add the value as a data field to the pivot table. Here is what I have come up with, but it is not working and I am not exactly sure why:
VBA Code:
Sub Refresh_Rolling_8_Week_Project_View()
'
' Refresh_Rolling_8_Week_Project_View Macro
'
'
Dim pt As PivotTable
Dim ptf As PivotField
Dim myDate As Date
'Set myDate variable to current system date
myDate = Date
'Set pt variable to the pivot table on the current active sheet
Set pt = ActiveSheet.PivotTables("PivotTable2")
'bypass errors when evaluating pivot fields in the for loop, this will bypass any pivot fields of data types other than dates and only evaluate date data type pivot fields
On Error Resume Next
'For each pivot field to evaluate if it is a date data type, and if date is within 56 days or 8 weeks from system date
For Each ptf In pt.DataFields
If ptf.Value < myDate Then
pt.PivotFields(ptf).Orientation = xlHidden
ElseIf ptf.Value < myDate + 56 Then
pt.PivotFields(ptf).AddDataField pt.PivotFields(ptf), "Sum of """ & ptf & """, xlSum"
Else
pt.PivotFields(ptf).Orientation = xlHidden
End If
Next ptf
End Sub
Any help you can provide is greatly appreciated. Thanks!