VBA Change Pivot Table Field: Error 1004

Question

The Project

I have a workbook that hosts weekly man-hour information. Everything is viewed and modified via a Userform. On the Userform I have a ListBox1 to show a PivotTableX of the associated week, so that I can easily see what the daily and total overtime hours are for the week. On my Userform I have two radio buttons that will alternate the PivotTableX to show Total Hours or Overtime Hours.

The Problem

When I click the radio button, to change the PivotField, I get an Error 1004. I've done a fair amount of searching but I can't find a solution that works. I might not understand the code behind Pivot Tables enough to know when a solution is right in front of me.

The Code

The following is the code that raises the error:

Private Sub OptionButton1_Change()
    Dim ws As Worksheet
    Dim wsName As String
    Dim pvtTable As PivotTable
    
    Set ws = ThisWorkbook.Sheets(SelSheet())
    ws.Select
    Set pvtTable = ws.PivotTables("PivotTable" & ws.Index)
    
    With pvtTable
        If OptionButton1.Value = True Then 'Total Time
            .AddDataField .PivotFields("TOTAL (W/O LUNCH)"), "Sum of TOTAL (W/O LUNCH)", xlSum
            .PivotFields("Sum of PREMIUM TIME").Orientation = xlHidden
        Else
            .AddDataField .PivotFields("PREMIUM TIME"), "Sum of PREMIUM TIME", xlSum
            .PivotFields("Sum of TOTAL (W/O LUNCH)").Orientation = xlHidden
        End If
    End With
End Sub

The .AddDataField lines throw the error. It makes me think that my code isn't finding the Pivot Table on the worksheet. To combat this, instead of using a PivotTables() index number, I call out for it specifically by name. When a new sheet is created, the Table and Pivot Table are created and numbered by the worksheet index. My worksheets will never change their index position as they are created in order from oldest to newest.

I also thought maybe the issue came from the sheet not being selected, as I've ran into similar issues with 1004 in the past, so I select the sheet but that did nothing.

Before you ask, SelSheet() is a simple function to return the name of the sheet based on the selection in ComboBox4 (the combobox next to "Week"). I'll include the snippet at the end (it works, it's not the issue.)

I've verified that the proper worksheet is being selected and that the names of the Table and Pivot Table are also correct via Debug.Print.

SelSheet() Function

Function SelSheet() As String
    Dim ws As Worksheet
    Dim wsName As String
    wsName = ComboBox4.Value
    Set ws = ThisWorkbook.Sheets(Replace(wsName, "/", ""))
    SelSheet = ws.Name
End Function

Information included by request from Comments Section

Debug.Print Results

Before you AddDataField use Debug.Print ws.Name & Debug.Print pvtTable.Name and check if the pivot table field list contains the field TOTAL (W/O LUNCH). Check for a leading or trailing space in the source data set — Subodh Tiwari sktneer
"I've verified that the proper worksheet is being selected and that the names of the Table and Pivot Table are also correct via Debug.Print." I also have checked the pivot table fields, they are properly named and show up on the worksheet. — Tyeler
Did you also check the field name in pivot table and the header in the source data for any leading or trailing spaces? Are they exactly same? — Subodh Tiwari sktneer
They are the same. I copied and pasted the source data header's just to be sure, because I thought that may have been the issue as well. — Tyeler
Okay then it should work. Can you create another simple sub routine on a standard module where you just set the worksheet and the pivot table and then adddatafield using the same syntax to see if that works? Of course no code for option button should be there. Just try to add the datafield via the code and see if you can. — Subodh Tiwari sktneer

Tyeler Tyeler · Accepted Answer · 2017-04-15T20:10:37

Solution

After a discussion with sktneer I found out what the issue was:

With Pivot Tables, if you try to .AddXxxxField a field that already exists on that Pivot Table in that location, you will receive the Error 1004.

Also if you try to set the orientation of a field to an orientation it already has, you'll receive Error 1004.

The following is the change I made to the code which cleared all the issues I was having:

With pvtTable
    For Each pfData In .DataFields
        pfData.Orientation = xlHidden
    Next pfData
    If OptionButton1.Value = True Then 'Total Time
        .AddDataField .PivotFields("TOTAL (W/O LUNCH)"), "Sum of TOTAL (W/O LUNCH)", xlSum
    Else
        .AddDataField .PivotFields("PREMIUM TIME"), "Sum of PREMIUM TIME", xlSum
    End If
End With

Thank you for your help sktneer!