VBA Pivot Table Issue

[OilEconomist]

Thanks in advance to anyone who can assist and I will provide feedback on suggested solutions.

For the code to make a Pivot Table, I'm getting the following error:
"Run-time error '1004':

"The PivotTable field name is not valid. To create a PivotTable report, you must use data that is organized as a list with labeled columns. If you are changing the name of the a PivotTable field, you must type a new name for the field."

On this line of code:

Set DataPivotTable = DataPivotCache.CreatePivotTable(TableDestination:=StartPivot, TableName:="PivotTable1")

The entire code is as follows:

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
    
'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("A1:" & 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




End Sub

 

[=ODIN=]

Sorry, your code is harder to read than one would think, despite all your comments.

I'm thinking that there are probably 2 possible problems here --

1. Your code is not passing a correct pivotsouce/cache to your pivot.
2. Your datasource for the pivot has a column header that is not compatible with pivot tables.


Please can you post all the values in the top row of your data source? Seeing these, we should be ale to rule out problem 2 listed above.

 

[OilEconomist]

Thanks =Odin= for your response. I am going to try a few things also to see if I can resolve the issue.

Here is the top row transposed into a column (I left a space between each entry).

Oil Volume Gross (Mbbl)


Gas Volume Gross (Mmcf)


NGL Volume Gross (Mbbl)


Oil Volume Net (Mbbl)


Gas Volume Net (Mbbl)


NGL Volume Net (Mbbl)


Oil Price ($/bbl)


Gas Price ($/MSCF)


NGL Price ($/gallon)


Oil & Gas Net Revenue ($M)


NGL Revenue ($M)


LOE Other Net ($M)


LOE Taxes Net ($M)


Capital Net ($M)


Free Cash Flow ($M)


Cumulative Free Cash Flow ($M)

 

[OilEconomist]

Hi =Odin=,

Based on your response, I think I have it figured out and since you don't have the data to try the code out with, let me debug and let you know.

Basically your comments have helped me resolve the issue!

"1. Your code is not passing a correct pivotsouce/cache to your pivot."
"2. Your datasource for the pivot has a column header that is not compatible with pivot tables."

I think I have extra data in a few of the columns where there is no heading so once I delete that, I think it will fix it.

Your post #2 was very helpful!

I will keep you posted.

 

[OilEconomist]

=Odin=

Thank you so much as that was the issue. The heading or lack there of one for three columns at the end which I didn't realize had data in some of the rows. I titled them and that fixed the issue.

If you could let me know why the code was hard to follow as I want to improve. I am new to VBA Coding. Just if you have time and don't mind.

Other than that! Thanks once again!

Post #2 resolved the issue.