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, Commented Sep 2, 2013 at 16:19

Community · Accepted Answer · 2017-05-23 12:24:33Z

2

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".

edited May 23, 2017 at 12:24

CommunityBot

11 silver badge

answered Sep 2, 2013 at 15:50

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Doug Glancy Over a year ago

Although an excellent suggestion, this isn't a specific answer to this specific question. It would make a good comment.

Beta Over a year ago

Thanks retailcoder! I'll check your suggestion. I've seen this problem faced by many. But the solution were not applicable to me.

Mathieu Guindon Over a year ago

Yeah, lots of pesky little things can go wrong when using a Range as a data source :) -- thanks for the tickmark!

Doug Glancy Over a year ago

Got my +1 too. Nice explanation of a better approach.

Mathieu Guindon Over a year ago

@B.ClayShannon you need to .Add to the sheet's .ListObjects collection. Specifically how that's done, certainly has a few Q&A's on this site. Or you can do it manually by selecting any cell in your table (range), and click "format as table" from the home ribbon tab.

|

Collectives™ on Stack Overflow

Pivot Table-VBA Error

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related