Excel VBA Run-time error '91'

Question

I'm trying to create a pivot table with VBA, and I got help from @Shai Rado to be able to create a empty pivot table, and then I added the value field, when I try to run it again, I got an error: Object variable or with block variable not set" for code "With .PivotFields("MedID")"

Which doesn't make sense to me because that part of code already created a empty pivot table and now it shows error.

Any thoughts? I'm totally new to VBA so I have many "silly" questions. I really appreciate any help!!

Option Explicit

Sub Create_Pivot_Table()

Dim wsData As Worksheet, wsPT As Worksheet
Dim PT_Cache    As PivotCache
Dim PT          As PivotTable
Dim PRng        As Range
Dim LastRow     As Long

With ThisWorkbook
   Set wsData = .Worksheets("Data")
   Set wsPT = .Worksheets("Pivot Table")
End With

LastRow = wsData.Cells(wsData.Rows.Count, 1).End(xlUp).Row
MsgBox LastRow ' <-- confirm value

Set PRng = wsData.Range("A1:O" & LastRow)

' option 2: Set the Pivot Cache
Set PT_Cache = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=PRng.Address(True, True, xlR1C1, True))

' set the Pivot Table
Set PT = PT_Cache.CreatePivotTable(wsPT.Range("D5"), "Pivot_Table_Test")

With PT



   With .PivotFields("MedID")
       .Orientation = xlRowField
       .Position = 1
       .LayoutBlankLine = False
       .Subtotals(1) = False
   End With

   With .PivotFields("TransactionType")
        .Orientation = xlColumnField
       .Position = 1
       .LayoutBlankLine = False
       .Subtotals(1) = False

   End With


   With .PivotFields("Quantity")
        .Orientation = xlDataField
        .Function = xlCount
        .Position = 1

    End With

Set PT = Nothing
Set PT_Cache = Nothing
Set wsData = Nothing
Set wsPT = Nothing
Exit Sub

End With

End Sub

Seems like the object PT is null. Ensure that this line: Set PT =CreatePivotTable(.... is returning a value and setting PT. — aguertin
Also try deleting the pivottable as the first line of code(if it exists). I am wondering if the reason its not working is because it already exists. — aguertin
@aguertin there's no need to delete the PivotTable if it exists, just to check if it is, if it does then all you need is to update it with the updated PivotCache, if it's not then Add the PivotTable — Shai Rado
@ShaiRado Agreed, but for the sake of knowing what the issue is it is much easier to delete then if that is in fact what is causing the issue, put a condition statement in to check if it already exists and update it instead. — aguertin

Shai Rado Shai Rado · Accepted Answer · 2017-04-13T17:36:17

Try the Updated code below (to fit your needs):

Option Explicit

Sub Create_Pivot_Table()

Dim wsData As Worksheet, wsPT As Worksheet
Dim PT_Cache    As PivotCache
Dim PT          As PivotTable
Dim PRng        As Range
Dim LastRow     As Long

With ThisWorkbook
   Set wsData = .Worksheets("Data")
   Set wsPT = .Worksheets("Pivot Table")
End With

LastRow = wsData.Cells(wsData.Rows.Count, 1).End(xlUp).Row    
Set PRng = wsData.Range("A1:O" & LastRow)

' Set the Pivot Cache
Set PT_Cache = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=PRng.Address(True, True, xlR1C1, True))

' add this line in case the Pivot table doesn't exit >> first time running this Macro
On Error Resume Next
Set PT = wsPT.PivotTables("Pivot_Table_Test") ' check if "Pivot_Table_Test" Pivot Table already created (in past runs of this Macro)

On Error GoTo 0
If PT Is Nothing Then
    ' create a new Pivot Table in "Pivot Table" sheet, start from Cell D5
    Set PT = wsPT.PivotTables.Add(PivotCache:=PT_Cache, TableDestination:=wsPT.Range("D5"), TableName:="Pivot_Table_Test")

Else ' just refresh the Pivot cache with the updated Range in "Data" sheet
    PT.ChangePivotCache PT_Cache
    PT.RefreshTable
End If

' === set Pivot Table fields ===
With PT
    With .PivotFields("MedID")
        .Orientation = xlRowField
        .Position = 1
        .LayoutBlankLine = False
        .Subtotals(1) = False
    End With
    With .PivotFields("TransactionType")
        .Orientation = xlColumnField
        .Position = 1
        .LayoutBlankLine = False
        .Subtotals(1) = False
    End With
    With .PivotFields("Quantity")
        .Orientation = xlDataField
        .Function = xlCount
        .Position = 1
    End With
End With

Set PT = Nothing
Set PT_Cache = Nothing
Set wsData = Nothing
Set wsPT = Nothing
Exit Sub

End Sub

Excel VBA Run-time error '91'

2 Answers