Hi all,
I've been searching this forum and the rest of the web for quite some time now but I still haven't found a solution to my problem. I hope someone can help me.
I get the following error: "Run-time error '1004': The Pivot Table field name is not valid. To create a Pivot Table report, you must use data that is organized as a list with labeled columns. If you are changing the name of the Pivot Table field, you must type a new name for the field."
On this line of code:
Set pvt = pvtCache.CreatePivotTable(TableDestination:=StartPvt, _
TableName:="PivotTable1")
The solution that is presented everywhere is that there is an empty hidden cell in the header row. I've checked this (multiple times) and this is not true. There are also no indentical cells in the header row. I can produce the pivot table manually, but I will need to perform this for several hundreds of files so I want to automate it.
This is my entire code:
Sub CreatePivotTable(fname As String, XlsFolder As String)
'PURPOSE: Creates a brand new Pivot table on a new worksheet from data in the ActiveSheet
Dim pvtsht As Worksheet
Dim datasht As Worksheet
Dim pvtCache As PivotCache
Dim pvt As PivotTable
Dim StartPvt As String
Dim SrcData As String
Dim LastRow As Integer
Dim LastColumnInt As Integer
Dim LastColumnStr As String
'Dim fName As String
Dim wbData As Workbook
'Dim extension As String
'fName = "ICP S22base-wk29_21APR17.xlsx"
'extension = ".xlsx"
Set wbData = Workbooks.Open(XlsFolder & fname, UpdateLinks:=False)
With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
LastColumnInt = .Cells(1, .Columns.Count).End(xlToLeft).Column
'function to get
LastColumnStr = GetColumnLetter(LastColumnInt)
End With
'Determine the data range you want to pivot
datasht = Replace(fname, ".xlsx", "")
SrcData = datasht & "!" & Range("A1:" & LastColumnStr & LastRow).Address(ReferenceStyle:=xlR1C1)
'Create a new worksheet
Set pvtsht = Sheets.Add
pvtsht.Name = "Draaitabel"
'Where do you want Pivot Table to start?
StartPvt = pvtsht.Name & "!" & pvtsht.Range("A3").Address(ReferenceStyle:=xlR1C1)
'Create Pivot Cache from Source Data
Set pvtCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, _
SourceData:=SrcData)
'Create Pivot table from Pivot Cache
'this is where the error occurs
Set pvt = pvtCache.CreatePivotTable(TableDestination:=StartPvt, _
TableName:="PivotTable1")
End Sub
Bookmarks