VBA Code for Pivot Table Sum Field Invalid Qualifier

[OilEconomist]

Thanks in advance for anyone's suggestions. I will give feedback on any suggested solutions.

Why am I getting a "Compile error: Invalid Qualifier" on "xlDataField" within the line:

'Add item to the Data Field to either Count or Sum. In this Case Sum
        With DataPivotTable.PivotFields("Gas.Volume.Gross").Orientation = xlDataField.Function = xlSum
            End With

When I comment out that part of the code it runs fine. The following is the entire code.

Sub CreatePivotTable()


'Dimensioning Variables
    Dim DataSourceSheet As Worksheet
    Dim DataPivotCache As PivotCache
    Dim DataPivotTable As PivotTable
    Dim StartPivot As String
    Dim PivotSourceData As String
    Dim ColumnLetter As String
    Dim LastRowDF As Long
    Dim LastColumnDF As Long
    Dim Wks As Worksheet




'Turn off Screen Mirroring/Updating
    Application.ScreenUpdating = False
    
'Ensure all Worksheets within this Workbook are calculated
    Application.Calculation = xlManual
    
'Checks to see if the new sheet exists "Pivot.Table.Data" and deletes it if so
    For Each Sheet In ActiveWorkbook.Worksheets
    If Sheet.Name = "Pivot.Table.Data" Then
          Sheet.Delete
     End If
    Next Sheet
    
'Loop Workbook to calculate all spreadsheets
    For Each Wks In ActiveWorkbook.Worksheets
        Wks.Calculate
    Next
        
        Set Wks = Nothing


'Activate "Date.Formatted" worksheet
    Worksheets("Data.Formatted").Activate
        
'Find last row
    LastRowDF = Cells.Find(What:="*", after:=Range("A1"), LookAt:=xlPart, LookIn:=xlFormulas, _
        SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row


        
'Finding last column
    LastColumnDF = Cells.Find(What:="*", after:=Range("A1"), LookAt:=xlPart, LookIn:=xlFormulas, _
        SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False).Column
        
'Convert To Column Letter
  ColumnLetter = Split(Cells(1, LastColumnDF).Address, "$")(1)


'Determine the data range to pivot
    PivotSourceData = Sheets("Data.Formatted").Name & "!" & Range("B2:" & ColumnLetter & LastRowDF).Address(ReferenceStyle:=xlR1C1)


'Creating the new destination sheet "Pivot.Table.Data" as the
    ActiveWorkbook.Sheets.Add(after:=Worksheets("Well.Attributes")).Name = "Pivot.Table.Data"
    Set DataSourceSheet = Sheets("Pivot.Table.Data")


'Where do you want Pivot Table to start?
    StartPivot = DataSourceSheet.Name & "!" & DataSourceSheet.Range("A3").Address(ReferenceStyle:=xlR1C1)


'Create Pivot Cache from Source Data
    Set DataPivotCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=PivotSourceData)


'Create Pivot table from Pivot Cache
    Set DataPivotTable = DataPivotCache.CreatePivotTable(TableDestination:=StartPivot, TableName:="PivotTable1")


'Calculate "Pivot.Table.Data"
    Sheets("Pivot.Table.Data").Calculate


'Screen Updating On
    Application.ScreenUpdating = True


'Add item to the Report Filter (Reserve Category)
    DataPivotTable.PivotFields("RC").Orientation = xlPageField
  
'Add item to the Column Labels (Date)
    DataPivotTable.PivotFields("Date").Orientation = xlColumnField
    
'Add item to the Row Labels (PHDWin Case Name)
    DataPivotTable.PivotFields("Well Name").Orientation = xlRowField
    
'Add item to the Data Field to either Count or Sum. In this Case Sum
        With DataPivotTable.PivotFields("Gas.Volume.Gross").Orientation = xlDataField.Function = xlSum
            End With
    
'Format Pivot Field
    'DataPivotTable.PivotFields("Year").NumberFormat = "#,##0"
    
'Turn on Automatic updates/calculations - like screenupdating to speed up code
    DataPivotTable.ManualUpdate = False






End Sub

 

[Jerry Sullivan]

Why am I getting a "Compile error: Invalid Qualifier" on "xlDataField" within the line:

'Add item to the Data Field to either Count or Sum. In this Case Sum
        With DataPivotTable.PivotFields("Gas.Volume.Gross").Orientation = xlDataField.Function = xlSum
            End With

That syntax for a With block is incorrect. The With keyword is used to reference an Object from a statement or multiple statements within the With block.

'Add item to the Data Field to either Count or Sum. In this Case Sum
With DataPivotTable.PivotFields("Gas.Volume.Gross")
   .Orientation = xlDataField
   .Function = xlSum
End With

The With block provides a more efficient form than this equivalent code...

'Add item to the Data Field to either Count or Sum. In this Case Sum
DataPivotTable.PivotFields("Gas.Volume.Gross").Orientation = xlDataField
DataPivotTable.PivotFields("Gas.Volume.Gross").Function = xlSum

 

[OilEconomist]

Hi Jerry and thanks for the post.

I used the following and it gave me the error the second line "Run-time error '1004' Unable to set the Function property of the PivotField class." When I went to the Pivot Table, everything seem to be fine. I couldn't understand what may have gone wrong.

'Add item to the Data Field to either Count or Sum. In this Case Sum
    DataPivotTable.PivotFields("Gas.Volume.Gross").Orientation = xlDataField
[B]    DataPivotTable.PivotFields("Gas.Volume.Gross").Function = xlSum[/B]

So you posted the following where for the second code you said
"The With block provides a more efficient form than this equivalent code...",

but on the first code you said:

"That syntax for a With block is incorrect. The With keyword is used to reference an Object from a statement or multiple statements within the With block."

so I was a bit confused. I took that you use the "With Block" is incorrect, but is more efficient. Well I tried the second code because the first one looked like what I submitted that it would not work.

That syntax for a With block is incorrect. The With keyword is used to reference an Object from a statement or multiple statements within the With block.

'Add item to the Data Field to either Count or Sum. In this Case Sum
With DataPivotTable.PivotFields("Gas.Volume.Gross")
   .Orientation = xlDataField
   .Function = xlSum
End With

The With block provides a more efficient form than this equivalent code...

'Add item to the Data Field to either Count or Sum. In this Case Sum
DataPivotTable.PivotFields("Gas.Volume.Gross").Orientation = xlDataField
DataPivotTable.PivotFields("Gas.Volume.Gross").Function = xlSum

 

[Jerry Sullivan]

Sorry if my explanation was unclear.

Your procedure should work if you use this code in lieu of your original code:

With DataPivotTable.PivotFields("Gas.Volume.Gross")
   .Orientation = xlDataField
   .Function = xlSum
End With

That worked for me, does it work for you?

The second code example I provided was intended to show you that when one uses a With block, it is equivalent to using a series of statements that repetitively list the same object (in this case, your PivotTable).
It turns out that in this case, those two code examples are not equivalent and the second example fails. I haven't seen that happen before and I suspect this is a rare exception that occurs when adding pivot table fields.

One other alternative is to use the AddDataField method.

 DataPivotTable.AddDataField DataPivotTable.PivotFields("Gas.Volume.Gross"), _
   "Sum of Gas.Volume.Gross", xlSum

 

[OilEconomist]

Thanks so much Jerry. I tested the codes and they both work.

I think the first code I was typing it in incorrectly although I tried like ten times.