Pivot Table-VBA Error

Question

I'm using the following code:

Sub MakeAPivotTable()

    Dim pt As PivotTable
    Dim cacheOfpt1 As PivotCache 'This is the Source Data
    Dim pf As PivotField
    Dim pi As PivotItem
    Dim LastCol As Long
    Dim LastRow As Long
    Dim PRange As Range


    LastCol = Sheets("Sheet2").Range("A1").SpecialCells(xlCellTypeLastCell).Column
    LastRow = Sheets("Sheet2").Range("A1").SpecialCells(xlCellTypeLastCell).Row
    Set PRange1 = Sheets("Sheet2").Cells(1, 1).Resize(LastRow, LastCol)

    On Error GoTo err_add_Y_next_row

    Sheets("Sheet3").Select
    ActiveSheet.PivotTables("PivotTable1").TableRange2.Clear  'Delete any Pivot Table

    'set the cache of PT
    Sheets("Sheet2").Select
    Set cacheOfpt1 = ActiveWorkbook.PivotCaches.Create(xlDatabase, PRange1)

    'create the pt
    Sheets("Sheet3").Select
    Set pt = ActiveSheet.PivotTables.Add(cacheOfpt1, Range("a1"), "PivotTable1")


    'put the Fields in

    With pt
    'add the Fields
    .PivotFields("CAMPAIGN").Orientation = xlRowField
    '.PivotFields("TAG1").Orientation = xlRowField
    '.PivotFields("TAG2").Orientation = xlRowField
    '.PivotFields("TAG3").Orientation = xlRowField
    .PivotFields("CIRN").Orientation = xlColumnField
    .PivotFields("RUN").Orientation = xlPageField  'Column Filter
    .PivotFields("TVSN").Orientation = xlDataField  'Value Field

    'set the number format
    .DataBodyRange.NumberFormat = "#,##0.00"


    'go to classic View
    .RowAxisLayout xlTabularRow
    End With

err_add_Y_next_row:
MsgBox Err.Number & Err.Description, _
    vbExclamation, "VBAtools.pl"

End Sub

But when I'm running the code, I'm getting the error "The PivotTable 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 a PivotTable field, you must type a new name for the field."

This code was running fine. But suddenly I started getting this error.

Can anybody please help me find the reason.

Thank you.

I suspect something happened to your source range, perhaps a column header (or an entire column?) was deleted by accident? — Mathieu Guindon

Mathieu Guindon Mathieu Guindon · Accepted Answer · 2013-09-02T15:50:20

Try using a ListObject ("Table") instead of a Range for a datasource; these always are a valid data source for a pivot table - see this SO question - the OP there wasn't having the same problem as you did, but I believe the same solution applies.

The reason why tables are always valid sources is because they always have valid headers, and they always have at least 1 row of data.

Once you have turned your range into a table (and called it, say, "Table1"), you can use it like this:

Set cacheOfPt1 = ActiveWorkbook.PivotCaches.Create(xlDatabase, "Table1")

With a cache that's using a valid data source, there shouldn't be a problem doing exactly what you're doing to Set pt:

Set pt = ActiveSheet.PivotTables.Add(cacheOfpt1, Range("a1"), "PivotTable1")

Also, with this setup you might want to reconsider the need to completely rebuild the pivot table from scratch every time; you can simply add/remove data to/from "Table1" and then refresh your pivot table - it will automatically be updated to reflect the contents of "Table1".

Pivot Table-VBA Error

1 Answers