VBA Pivot Table Issue

OilEconomist

Active Member
Joined
Dec 26, 2016
Messages
421
Office Version
  1. 2019
Platform
  1. Windows
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:
Code:
Set DataPivotTable = DataPivotCache.CreatePivotTable(TableDestination:=StartPivot, TableName:="PivotTable1")


The entire code is as follows:

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
    
'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
 

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.
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.
 
Upvote 0
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)
 
Upvote 0
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.
 
Upvote 0
=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.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

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
Back
Top