VBA Creating Pivot Table error 1004

Question

I am trying to have a program create several pivot tables for me (since I'd have to do this weekly/monthly) but same type of data.

Based on several websites, I copied some code and edited some code for the following:

Private Sub CommandButton1_Click()

'Declaration



'Creates new worksheet

Dim sht As Worksheet
Dim pvtCache As PivotCache
Dim pvt As PivotTable
Dim StartPvt As String
Dim SrcData As String
Dim DataRange As Range

'Create a new worksheet
  Set sht = Sheets.Add
  sht.Name = "EUPS Report"

'Determine the data range you want to pivot
  Set DataRange = Application.InputBox("Please Select the Data (Please select by pressing any cell in the table then pressing ctrl+a)", Default:=Selection.Address, Type:=8)
  SrcData = sht.Name & "!" & DataRange.Address(ReferenceStyle:=xlR1C1)

'Where do you want Pivot Table to start?
'  StartPvt = "'" & sht.Name & "!" & sht.Range("A15").Address(ReferenceStyle:=xlR1C1) & "'"

'Create Pivot Cache from Source Data
  Set pvtCache = ActiveWorkbook.PivotCaches.Create( _
    SourceType:=xlDatabase, _
    SourceData:=SrcData)

'Create Pivot table from Pivot Cache
  Set pvt = pvtCache.CreatePivotTable( _
    TableDestination:=Worksheets("EUPS Report").Range("A15"))
'   TableName:="PivotTable1")

End Sub

However, I'm running into this 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 a PivotTable field, you must type a new name for the field.

I cannot show you my data since it's company information, but it's a regular data table with column headers but no row headers. I have tested this code on other data but it doesn't work either. And I have tested my data with manually created pivot tables and they do work, so it's the code's problem.

Kindly let me know what's wrong. Thanks!

Answer 1

Use Excel Table Objects to store the data, then you can reference the table by name as the source of the pivot tables. The table will grow and shrink with the data and you only need to refresh the pivot table when the data has changed.

You can manually copy and paste the data into the table, or you can use Power Query to get the data from almost any data source. Power Query is a free add-in from Microsoft for Excel 2010 and 2013 and it is built into Excel 2016 as Get&Transform.

The idea here is that it is easier and more efficient to just refresh the data than having to build pivot tables from scratch every week. That really defeats the purpose of pivot tables. Work smarter, not harder.