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