When I am trying to run the following code I am getting an error of:
Run-Time error '91':
Object variable or With block variable not set
Run-Time error '91':
Object variable or With block variable not set
VBA Code:
Sub CreatePivot()
Dim nr As Range
Dim pt As PivotTable
Dim ws As Worksheet
Dim LastRow As Long
For Each ws In Worksheets
Select Case ws.Name
Case "Data0", "Data1", "Data2", "Data3", "Data4", "Data5"
With ws
LastRow = .Range("N" & .Rows.Count).End(xlUp).Row
nr = ws.Range("A2" & ":O" & LastRow)
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
ws.Name & nr, Version:=3) _
.CreatePivotTable TableDestination:=ws.Name & "A" & LastRow + 10, _
TableName:="PivotTable4", DefaultVersion:=xlPivotTableVersion14
Set pt = ws.PivotTables("PivotTable4")
pt.AddDataField pt.PivotFields("Code"), "Sum of Code", xlSum
With pt.PivotFields("SessionDate")
.Orientation = xlRowField
.Position = 1
End With
With pt.PivotFields("Session")
.Orientation = xlRowField
.Position = 2
End With
With pt.PivotFields("Probe#")
.Orientation = xlRowField
.Position = 3
End With
End With
Case Else
'Do Nothing
End Select
Next ws
End Sub