VBA: Error on creating a pivot table

Question

I am creating a VBA macro. Among other things, it makes a pivot table:

    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=Sheets("Working").UsedRange).CreatePivotTable TableDestination:="", TableName:="HDPivotTable", DefaultVersion:=xlPivotTableVersion10

This works, but when I delete all data from the Excel spreadsheet and replace it with new data of the same structure, I get an error on the above line:

Run-time error '13': Type mismatch

Could you please help me on this?

Thanks.

EDITED:

After applying Scott's suggestion, I get another error:

With Worksheets("Working")
    lRow = .Range("A" & .Rows.Count).End(xlUp).Row
    lColumn = .Range("A" & .Columns.Count).End(xlToLeft).Column
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=.Range(.Cells(1, 1), .Cells(lRow, lColumn))).CreatePivotTable TableDestination:="", TableName:="HDPivotTable", DefaultVersion:=xlPivotTableVersion10
End With

ActiveSheet.Name = "HD Pivot"
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(1, 1)
ActiveSheet.PivotTables("HDPivotTable").AddFields RowFields:="Location Code"

The error is: Run-time error '1004': AddFields method of PivotTable class failed

what if you make TableName random/unique each time you run the code? — Kazimierz Jawor
@KazimierzJawor You see, I could run the code as many times as needed without problems. The problem started when I replaced the data... — David Shochet
I found the solution here: stackoverflow.com/questions/25870193/… — David Shochet

Scott Holtzman Scott Holtzman · Accepted Answer · 2016-02-17T16:19:33

Most likely what is causing the error is the use of UsedRange. While a nifty tool, it also has its drawbacks, especially since you are now replacing data.

For example, if the new data has less columns then the old data, or even less rows, the UsedRange will most likely reflect the old range with data. Furthermore, depending upon what you have been doing with your worksheet there may be cells that contain formatting or other characters that you cannot see with the naked-eye, that will affect the UsedRange.

If you make the following adjustments in your code, it should work every time.

Dim ws as Worksheet
Set ws = Worksheets("Working")

Dim lRow as Long, lColumn as Long

'assumes data table of contiguous rows/columns starting in A1
With ws
    lRow = .Range("A" & .Rows.Count).End(xlup).Row
    lColumn = .Cells(1, .Columns.Count).End(xltoLeft).Column
End With

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=ws.Range(ws.Cells(1,1),ws.Cells(lRow,lColumn)) ...

VBA: Error on creating a pivot table

1 Answers