VBA: Type Mismatch with PivotCaches

Question

I am trying to insert a pivot table into a document. The following code throws a mismatch error, however, I am not sure where/why it is throwing it within the line (the Set pCache... line. While the error is thrown, the blank pivot table is still created.

Where am I going wrong?

    pivot_lastRow = mainSheet.Cells(mainSheet.Rows.count, "A").End(xlUp).Row 'may need to change "A"
    pivot_lastCol = mainSheet.Cells(1, mainSheet.Columns.count).End(xlToLeft).Column

    Set pivotRange = mainSheet.Range(mainSheet.Cells(1, 1), mainSheet.Cells(pivot_lastRow, pivot_lastCol))

    Set pCache = wbk.PivotCaches.Create(SourceType:=xlDatabase, _
             SourceData:=pivotRange).CreatePivotTable(TableDestination:=pivotSheet.Cells(2, 2), _
             TableName:="Test")

Answer 1

You are creating a pivot table and try to fit it into a pivot cache object, that can't work! ;)

Separate the last statement:

Dim pCache As PivotCache
Dim pTable As PivotTable

 pivot_lastRow = mainSheet.Cells(mainSheet.Rows.count, "A").End(xlUp).Row 'may need to change "A"
    pivot_lastCol = mainSheet.Cells(1, mainSheet.Columns.count).End(xlToLeft).Column

    Set pivotRange = mainSheet.Range(mainSheet.Cells(1, 1), mainSheet.Cells(pivot_lastRow, pivot_lastCol))

    Set pCache = wbk.PivotCaches.Create(SourceType:=xlDatabase, _
             SourceData:=pivotRange)

    Set pTable = pCache.CreatePivotTable(TableDestination:=pivotSheet.Cells(2, 2), _
             TableName:="Test")