Loop through pivot fields and add them as data fields based on if conditions

n8n

New Member
Joined
Aug 25, 2020
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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:

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!
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Watch MrExcel Video

Forum statistics

Threads
1,129,371
Messages
5,635,876
Members
416,886
Latest member
coreyalaurence37

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top