createpivottable cannot set

[mamafich]

Hi,

I'm trying to create a pivottable on VBA , I've set my range and pivot cache but I cannot create pivot table, it shows = nothing when I watch its value. At the last row of the pasted code below I receive the error "Run Time error 91, Object Variable not set". I couldn't find what is wrong with the code.

Thanks a lot,

Dim ws As Worksheet
Dim ptCache As PivotCache
Dim pt As PivotTable
Dim prange As Range
Dim lastrow As Long
Dim lastcol, lastpivot As Long
Dim i, j As Long
Set ws = Worksheets(1)


For Each pt In ws.PivotTables
    pt.TableRange2.Clear
Next pt


lastrow = ws.Cells(Rows.Count, 1).End(xlUp).Row
lastcol = ws.Cells(1, Columns.Count).End(xlToLeft).Column


Set prange = ws.Cells(1, 1).Resize(lastrow, lastcol)
Set ptCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
    SourceData:=prange)
    
Set pt = ptCache.CreatePivotTable(TableDestination:=ws.Cells(2, lastcol + 2), TableName:="PivotTable")
        
pt.ManualUpdate = True

 

[Jerry Sullivan]

Hi and Welcome to the Board,

That code worked for me and I wasn't able to replicate the error you describe.

What version of Excel are you using?

What happens when you watch the value of prange?