Error 440: Method 'Create' of object 'PivotCaches' failed

Question

I have been trying to create a macro that populates a pivot table. However, I keep getting this

Run-Time Error 440

on the Set Cache line.

Previously, I was running into other run time errors but those were easy fixes. I do not understand why the PivotCaches.Create method is not allowed in this case. SOS, please help!

Here is the code:

' Declare variables for pivots
Dim pivotWB As Workbook
Dim RawDataWS As Worksheet
Dim dataLocation As Range
Dim cache As PivotCache
Dim table As PivotTables

' name and set the workbook for pivot tables
folderName = ThisWorkbook.Sheets(1).Cells(5, 13).Value
fileName = ThisWorkbook.Sheets(1).Cells(6, 13).Value
extName = ThisWorkbook.Sheets(1).Cells(7, 13).Value
wbAddress = folderName & "\" & fileName & "." & extName
Set pivotWB = Workbooks.Open(wbAddress)

Set dataLocation = pivotWB.Sheets("Raw Data").Range("A:AK")

Set cache = pivotWB.PivotCaches.Create(SourceType:=xlDatabase,_
sourceData:=dataLocation, Version:=xlPivotTableVersion14)

For i = 1 To 5

' create new worksheet and pivot table
pivotWB.Sheets.Add Before:=Sheets(1)
pivotWB.Sheets(1).Name = Left(toPivot(i), 30)
cache.CreatePivotTable TableDestination:=pivotWB.Sheets(1).Cells(1, 1), TableName:=toPivot(i) & " Pivot Table"

' set rows
With pivotWB.Sheets(1).PivotTables(toPivot(i) & " Pivot Table").PivotFields("Rejection Category Description")
    .Orientation = xlRowField
    .Position = 1
End With

' set columns
With pivotWB.Sheets(1).PivotTables(toPivot(i) & " Pivot Table").PivotFields("Post Period")
    .Orientation = xlColumnField
    .Position = 1
End With

' set values
With pivotWB.Sheets(1).PivotTables(toPivot(i) & " Pivot Table").PivotFields("Procedure Code")
    .Orientation = xlDataField
    .Position = 1
    .Function = xlCount
    .NumberFormat = "#,##0"
    .Name = "Count of Code"
End With
With pivotWB.Sheets(1).PivotTables(toPivot(i) & " Pivot Table").PivotFields("Amount")
    .Orientation = xlDataField
    .Position = 2
    .Function = xlSum
    .NumberFormat = "$#,##0"
    .Name = "Sum of Amount"
End With

Next i

Answer 1

Following @Rory comment, you need to set the PivotCache object using the Address property.

Also, setting your table object to your PivotTable will create a much easier and shorter code.

See code below, more notes inside the code's comments.

Modified Code

' --- set the Pivot Cache ---
Set cache = pivotWB.PivotCaches.Create(SourceType:=xlDatabase, _
            SourceData:=dataLocation.Address(False, False, xlR1C1, xlExternal))

For i = 1 To 5
    ' create new worksheet and pivot table
    pivotWB.Sheets.Add Before:=Sheets(1)
    pivotWB.Sheets(1).Name = Left(toPivot(i), 30)

    ' --- set the Pivot-Table Object ---
    Set table = cache.CreatePivotTable(TableDestination:=pivotWB.Sheets(1).Cells(1, 1), TableName:=toPivot(i) & " Pivot Table")

    With table ' <-- after you set the table object, you can simply use With statement now

        ' set rows
        With .PivotFields("Rejection Category Description")
            .Orientation = xlRowField
            .Position = 1
        End With

        ' set columns
        With .PivotFields("Post Period")
            .Orientation = xlColumnField
            .Position = 1
        End With

        ' set values
        With .PivotFields("Procedure Code")
            .Orientation = xlDataField
            .Position = 1
            .Function = xlCount
            .NumberFormat = "#,##0"
            .Name = "Count of Code"
        End With
        With .PivotFields("Amount")
            .Orientation = xlDataField
            .Position = 2
            .Function = xlSum
            .NumberFormat = "$#,##0"
            .Name = "Sum of Amount"
        End With
    End With
Next i