Is there really an ActiveSheet?

--
George Nicholson

Remove 'Junk' from return address.


"Darrell Wesley" wrote in message
...
I have a visual basic application that creates a Excel spreadsheet and
then creates a pivot table from the data found on Sheet1. The first pass
thru the code everything works fine but if I send it thru this section of
code again without closing the application I get an:

Run_Time error '91'

Object variable or with block variable not set

The code for the pivot table creation is as follows:

With ObjExcel
.ActiveWorkbook.PivotCaches.Add(SourceType:=xlData base, SourceData:= _
"Sheet1!R1C1:R" & row1 & "C20").CreatePivotTable
TableDestination:="", TableName:= _
"PivotTable1"
.ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3,
1)
.ActiveSheet.Cells(3, 1).Select
With .ActiveSheet.PivotTables("PivotTable1")
.ColumnGrand = False
.RowGrand = False
.SmallGrid = False
End With
.ActiveSheet.PivotTables("PivotTable1").AddFields
RowFields:=Array("Run#", _
"WMW", "WML", "COIL HGT")
.ActiveSheet.PivotTables("PivotTable1").PivotField s("Qty").Orientation
= _
xlDataField

.ActiveSheet.PivotTables("PivotTable1").PivotField s("WML").Subtotals =
Array( _
False, False, False, False, False, False, False, False, False,
False, False, False)

.ActiveSheet.PivotTables("PivotTable1").PivotField s("WMW").Subtotals =
Array( _
False, False, False, False, False, False, False, False, False,
False, False, False)

.ActiveSheet.PivotTables("PivotTable1").PivotField s("Run#").Subtotals
= Array( _
False, False, False, False, False, False, False, False, False,
False, False, False)
.ActiveSheet.PivotTables("PivotTable1").PivotField s("Count of
Qty").Function = _
xlSum
.ActiveSheet.PivotTables("PivotTable1").PrintTitle s = True
.ActiveSheet.Name = "BOXES"
End With

The line that gets the error is

.ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)

Any ideas as to what is happening?