Thread: Differentiate between column fields and data fields in a pivot table

Hello,

I am trying to manage the format for a pivot table that we receive from a customer every week. In order for my macro to function correctly, I need to be able to format the pivot table to the expected format, with the expected pivot row fields and pivot column fields. I've been able to do this for rows fields, and for format using some code for autoformat, etc., but not for data the column fields.

The data fields change once a month (which is expected and compensated for in my macro), but I need to be able to remove any column fields without removing any data fields using VBA.

Therefore, I have tried the following code to remove column fields:

Private Sub removepivotfields(pvtable As PivotTable)
    Dim pvtfield As PivotField
    For Each pvtfield In pvtable.ColumnFields
        pvtfield.Orientation = xlHidden
    Next
End Sub

However, this also for some reason deletes my fields in pivotdata as well.

To compensate, I tried the following code:

Private Sub removepivotfields(pvtable As PivotTable)
    Dim datafields() As String        
    Dim pvtfield As PivotField
    ReDim datafields(1 To 1)
    For Each pvtfield In pvtable.datafields
        storedatafields pvtfield.Name, datafields
    Next
    For Each pvtfield In pvtable.ColumnFields
        If checkdatafields(pvtable.Name, datafields) = True Then
            pvtfield.Orientation = xlHidden
        End If
    Next
End Sub

Private Function checkdatafields(pivottablename As String, datafields As Variant) As Boolean
    Dim i As Integer
    checkdatafields = False
    For i = LBound(datafields) To UBound(datafields)
        If datafields(i) = pivottablename Then
            checkdatafields = True
            Exit For
        End If
    Next
End Function

Private Sub storedatafields(ByRef pvfieldname As String, datafields As Variant)
    Dim i As Integer
    
    If datafields(1) = "" Then
        i = UBound(datafields)
    Else
        i = i + 1
    End If
    ReDim Preserve datafields(1 To i)
    datafields(i) = pvfieldname
End Sub

However, now it will not remove any column fields, thinking that they are data fields.

Is there any way in VBA to systematically differentiate between column fields and data fields so I can remove column fields while leaving data fields as is?

Thanks.

Regards,

William

Hi All,

I found my answer. Apparantly (at least in Excel 2003 and I'm assuming the same in Excel 2007), if more than one pivot field is loaded as data, than rather then having a pivot field in the data fields area, Excel makes a pivot field called Data and places it in the columns area.

Thus, all I needed in my code was

Private Sub removepivotfields(pvtable As PivotTable)
    Dim pvtfield As PivotField
    For Each pvtfield In pvtable.ColumnFields
        If UCase(pvtfield.Name) <> UCase("data") Then
            pvtfield.Orientation = xlHidden
        End If
    Next

Thanks.