Trying to create a PivotTable with VBA, constantly getting an error.

[phroureo]

Hello everyone, I've tried a few different methods, including recording a macro while creating a PivotTable, using a similar function from a website, and most currently using a guid I found on thespreadsheetguru to create a Pivot Table using VBA. (I would link it, but I don't have enough posts yet.)

Unfortunately, I am having an issue with the creation. Here is my code:


'' Macro3 Macro
'


'


Dim pvtTbl As PivotTable
Dim wsData As Worksheet
Dim mgData As ListObject
Dim pvtTblCache As PivotCache
Dim wsPvtTbl As Worksheet
Dim pvtFld As PivotField
Dim i As Integer
Dim j As Integer
Dim startPvt As String




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


'set the starting point of the pivot table
startPvt = wsPvtTbl.Name & "!" & wsPvtTbl.Cells(i, 1)




'create the pivot cache
Set pvtTblCache = ActiveWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:="Table_Name")


Set pvtTbl = pvtTblCache.CreatePivotTable( _
TableDestination:=startPvt, _
TableName:="PivotTable6")
End Sub


I know I have a lot of frivolous variables in there, but I'm eventually going to have it creating at least 15+ different pivottables on a regular basis, which is why I don't want to do it by hand.

The error it is returning is:

"Run-time error '5':

Invalid procedure call or argument"


Microsoft's support says that this is essentially when the data is too big for the variable type. I fail to understand how that is relevant in my case, because I'm still new to VBA.

The error occurs in the "Set pvtTbl" line at the end, according to the debug tool.

If anyone is able to help me figure out what the heck is going on, it would be much appreciated.



Edit:

I have come up with a solution (as so often happens just a few minutes after posting on a forum after spending 4+ hours on an issue.)


Sub Macro3()'
' Macro3 Macro
'


'


Dim pvtTblCache As PivotCache
Dim pvtTbl As PivotTable
Dim pvtFld As PivotField
Dim mgData As ListObjects
Dim wsData As Worksheet
Dim wsPvtTbl As Worksheet
Dim i As Integer
Dim j As Integer
Dim startPvt As String
Dim tableArray As Variant
Dim rng As Range


i = 1
j = 1


'What worksheet contains the data
Set wsData = Sheet1


'creates range from table
Set rng = Sheet1.Range("A1").CurrentRegion


'where the new pivot table will be created
Set wsPvtTbl = Sheet2


'set the starting point of the pivot table
startPvt = Sheet2.Cells(i, 1)




'create the pivot cache
Set pvtTblCache = ActiveWorkbook.PivotCaches.Create(xlDatabase, rng)


Set pvtTbl = pvtTblCache.CreatePivotTable(startPvt, "PivotTable" & j)

End Sub