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

Answer 1

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      

Answer 2

With this error it looks like PT is not set to PivotTable, so there is nothing to refer like With .PivotFields("MedID"). Create PT and the refer PT to your pivottable with name like Set PT = ThisWorkbook.PivotTables("PivotName")