Can not create pivot table by VBA but I can manually create pivot table from same data source

Question

    Sub CreatePivot()
      Dim NewSheet As Worksheet
      Dim PTCache As PivotCache
      Dim PT As PivotTable
      Dim PTRange As Range
      Dim FinalRow As Long
      Dim FinalCol As Long
    
       Set NewSheet = Workbooks("東京威力_樞紐分 
       析.xlsm").Sheets.Add(Before:=Workbooks("東京威力_樞紐分 
        析.xlsm").Worksheets(1))
       NewSheet.Name = "Summary"
        
       For Each PT In NewSheet.PivotTables
         PT.TableRange2.Clear
       Next PT
        
       Set PTRange = getTable("Table1").Range
    
      'Create PivotCaches
       Set PTCache = Workbooks("東京威力_樞紐分
       析.xlsm").PivotCaches.Create(SourceType:=xlDatabase, 
       SourceData:=PTRange.Address)
        
    
      'Create PivotTable
       Set PT = 
       PTCache.CreatePivotTable(TableDestination:=NewSheet.Cells(2, 
       2),tableName:="PivotTable1")
        
       'ManualUpdate On
       PT.ManualUpdate = True
    
       PT.AddFields RowFields:=Array("Recharge BU", "Main Category"), 
       ColumnFields:="Recharge To"
    
       With PT.PivotFields("Hours")
        .Orientation = xlDataField
        .Function = xlSum
        .Position = 1
        .NumberFormat = "#,##0.00"
        .Name = "Total - Hours"
       End With
    
      'Calcu PivotTable
      PT.ManualUpdate = False
      PT.ManualUpdate = True

    End Sub
-------------------------------------------------------------------------
     Function getTable(tableName As String) As ListObject
        Dim FinalRow As Long
    
        With Workbooks("東京威力_樞紐分析.xlsm").Worksheets("原始資料")
          On Error GoTo ErrorHandler
            FinalRow = .Cells(Rows.Count, 1).End(xlUp).row
            Set getTable = .ListObjects(tableName)
        End With
     Exit Function
     
     ErrorHandler:
        Debug.Print Err.Number; ":" & Err.Description
     
        If getTable Is Nothing Then
        Workbooks("東京威力_樞紐分析.xlsm").Worksheets("原始資料").ListObjects.Add(xlSrcRange, 
        Workbooks("東京威力_樞紐分析.xlsm").Worksheets("原始資料").Range("A1:AK" & FinalRow), , xlYes).Name = "Table1"

        Set getTable = Worksheets("原始資料").ListObjects(tableName)
     End If
     Resume Next

    End Function

Hi Folks, the Purpose of the code is to create pivot table. But I Got Error Message 『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』 when I created Pivot Table. (at the line==> PTCache.CreatePivotTable)

What could possibly go wrong ? Thanks

PS : I can manually create pivot table from same data source.

I would cheat by recording a macro for creating a PT in your scenario then refer to that code. — Rosetta
I found that the code is modified as follows " 『Set PTCache = Workbooks("東京威力_樞紐分析.xlsm").PivotCaches.Create(SourceType:=xlDatabase, SourceData:="Table1")』, then it works. But I still don't understand what's wrong with my previous code. — TsunHsiao

Rosetta Rosetta · Accepted Answer · 2021-08-24T06:14:59

SourceData:=PTRange.Address will give us the cell address without the sheet name, e.g. $C$7:$G$17. This do not work for PT.

SourceData requires range reference with sheet name, e.g.

Therefore, it should be SourceData:= PTRange.Parent.Name & "!" & PTRange.Address. But if your sheet name contain spaces, then it should be SourceData:= "'" & PTRange.Parent.Name & "'!" & PTRange.Address.

Since your data is contained in named-range Table1, using the name as the SourceData will be much much easier.

Can not create pivot table by VBA but I can manually create pivot table from same data source

1 Answers