VBA - PivotTable - Cannot Create Cache

[ryanmorris]

UPDATE - I changed PRange to PRange.Address in the Set PTCache line of code and it worked. Can anyone explain the technical reason behind this?

Good afternoon everyone,

I am having the damndest time trying to have a PivotTable created via VBA. Here is the setup:

I have a sheet with one tab called Source
This tab has source information consisting of columns A:AF and a varrying # of rows each day.

My goal is to create two new worksheets and have a PivotTable appear on each. They both show pretty much the same data with the exception of one sheet having an additional data field. I am using Excel 2010.

I've broken the code into three blocks (I will add blocks 2 and 3 if necessary but I feel like they aren't causing the problem):
1) Establishes static variables such as sheet names and output variables
2) Creates the first sheet and PivotTable on it
3) Creates the second sheet and PivotTable on it

My code continuously fails at the "Set PTCache = " line

Dim WSSource As Worksheet
Set WSSource = Worksheets("Source")

Dim PTCache As PivotCache
Dim PRange As Range

Dim LastRowSource As Long
Dim LastColSource As Long

LastRowSource = WSSource.Cells(Application.Rows.Count, 1).End(xlUp).Row
LastColSource = WSSource.Cells(1, Application.Columns.Count).End(xlToLeft).Column
        
'set PRange here
Set PRange = WSSource.Cells(1, 1).Resize(LastRowSource, LastColSource)

----------This is the line that fails----------
Set PTCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=PRange, Version:=xlPivotTableVersion14)
-------------------------------------------
        
Dim PTOutput1 As Worksheet
Worksheets.Add.Name = "One Day Old"
Set PTOutput1 = Worksheets("One Day Old")
       
Dim PTOutput2 As Worksheet
Worksheets.Add.Name = "30 Days Old"
Set PTOutput2 = Worksheets("30 Days Old")
        
Dim ptOneDay As PivotTable
Dim pt30Day As PivotTable

I've scoured the net and tried different combinations of suggested changes but they all generate the same error - Run-Time Error 5, Invalid Procedure Call.

Thanks guys.