Creating a PivotTable with VBA returning Run-Time error 1004

[phroureo]

As the title says, when I try and create a PivotTable using a VBA function (I'm using the first method on this page), it returns the following error:

"Run-time error '1004':

Application-defined or object-defined error"

According to Microsoft's page about the error that it is a catch-all for anything that doesn't have a specific error, so I don't know what to do differently.

I'm going to be creating multiple pivottables in a "for" function, which is why I have it being mapped to (i, "1") and "PivotTable" & j instead of just the names.

Here is the code that I have now (I haven't even put in the "For" loop yet)

Sub Macro3()'
' Macro3 Macro
'


'


Dim pvtTbl As PivotTable
Dim wsData As Worksheet
Dim mgData As Range
Dim pvtTblCache As PivotCache
Dim wsPvtTbl As Worksheet
Dim pvtFld As PivotField
Dim i As Integer
Dim j As Integer


i = 1
j = 1


'What worksheet contains the data
Set wsData = Worksheets("Data Import")


'where the new pivot table will be created
Set wsPvtTbl = Worksheets("Profit and Loss Statement")


'create the pivot cache
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
        SourceData:="Table_name", _
        Version:=xlPivotTableVersion12).CreatePivotTable _
 _
        TableDestination:=wsPvtTbl.Cells(i, "1"), _
        TableName:="PivotTable" & j, _
        DefaultVersion:=xlPivotTableVersion12
    End Sub

The error is (obviously) happening on the "create pivot cache" section. Unfortunately, you can't break up the pivot table creation by row, so I can't get more specific. Also, I've tried using a macro to create a PivotTable and then immediately deleting it and rerunning the macro, but that returns the same 1004 error.

 

[phroureo]

I've tried a few different solutions with no success. Any thoughts?

 

[Domenic]

Try replacing...

TableDestination:=wsPvtTbl.Cells(i, "1")

with

TableDestination:=wsPvtTbl.Cells(i, [COLOR=#ff0000]1[/COLOR])

Hope this helps!