Object Variable or with Block not set

[Djblois]

Here is my code:

Set findString = Range("C1").Find(What:="Sales Report")
Set wB(1) = ActiveWorkbook
Application.ScreenUpdating = True
On Error Resume Next
Set detail = Worksheets("detail")
If findString Is Nothing Then
If detail Is Nothing Then
cantrun.Show
End
Else
Set findString = detail.Range("D1").Find(What:="Whse")
If findString Is Nothing Then
cantrun.Show
End
Else
SalesReports.Show
End
End If
End If
End If
Set findString = Nothing
Set detail = wB(1).ActiveSheet
detail.Name = "Detail"
Blinco
Application.ScreenUpdating = True
'Create sheet
On Error Resume Next
SalesReports.Hide
myInput = InputBox("What do you want to name the Report?")
If (myInput) = "" Then
MsgBox "You did not Create a Sales Report. Press Blinco button to rerun report."
End
Else
End If

Set pvt = Worksheets.Add(, detail, 1)
pvt.Name = myInput
pvt.Activate

finalRow = detail.Cells(65536, 1).End(xlUp).Offset(-1, 0).Row
Set pRange = detail.Cells(1, 1).Resize(finalRow, 21)
Set ptCache = wB(1).PivotCaches.Add(SourceType:=xlDatabase, SourceData:=pRange.Address)
Set pt = ptCache.CreatePivotTable(TableDestination:=Range("A1"), TableName:=myInput)
pt.RowGrand = False
pt.ManualUpdate = True


then it loads a form and here is the code in the form

If CasesDollars.Cases.Value = True Then
With pt.PivotFields("Cases")
.Orientation = xlDataField
.Function = xlSum
.NumberFormat = "#,##0"
End With
End If
If CasesDollars.Units.Value = True Then
With pt.PivotFields("Units")
.Orientation = xlDataField
.Function = xlSum
.NumberFormat = "#,##0"
End With
End If
If CasesDollars.Amount.Value = True Then
With pt.PivotFields("Amt ($)")
.Orientation = xlDataField
.Function = xlSum
.NumberFormat = "#,##0"
End With
End If
If CasesDollars.Cost.Value = True Then
With pt.PivotFields("Total Cost ($)")
.Orientation = xlDataField
.Function = xlSum
.NumberFormat = "#,##0"
End With
End If
If CasesDollars.Profit.Value = True Then
With pt.PivotFields("Profit ($)")
.Orientation = xlDataField
.Function = xlSum
.NumberFormat = "#,##0"
End With
End If
If CasesDollars.Price.Value = True Then
With pt.PivotFields("Price ($)")
.Orientation = xlDataField
.Function = xlAverage
.NumberFormat = "Comma"
End With
End If

After I use this to create one pivottable off of a Spreadsheet it gives me the "Object Variable or with block not set" error on line

With pt.PivotFields("Cases")

I don't understand why as pt is set and it always works the first time and it used to work. Please Help, this is very important

[matthewspatrick]

Unless you dimmed pt as a global public variable, you are getting that error because you went out of scope. That is, if you dimmed pt only in the original sub, then the UserForm code has no idea that pt exists.

When I need to have some variable, scalar or object, be available in both "regular" code and UserForm code, I usually set up that variable in the declarations section of my regular module:


' top of module

Option Explicit
Option Compare Text

Public pt As PivotTable
' Other declarations as needed

' Now do your first Sub/Function

[Djblois]

I already have the Pt declared publically with option explicit above it and it still doesn't work.

[matthewspatrick]

I already have the Pt declared publically with option explicit above it and it still doesn't work.

OK, but did you also declare pt as a variable within your UserForm code (either in the sub that your code sample was from, or as a module-level variable in the UserForm's code module)? If you did, then comment that out.

[Djblois]

I actually just did a test and the reason why is for some reason it is not creating the Pivot Table.

[Djblois]

If I already added a pivot table with my code then it will not add another pivot table with my code. However, if I delete the first one than I can add another one.

[matthewspatrick]

I suspect what happened was that when your code tried to add a PivotTable named <myInput>, that operations failed because you already had a PivotTable with the same name. Because the original sub had


On Error Resume Next


you never would have seen the error that I believe this line tripped:


Set pt = ptCache.CreatePivotTable(TableDestination:=Range("A1"), TableName:=myInput)