Error in Excel VBA Code (Error 91)

[dailem]

I have written a section of code that extracts all data from a
worksheet & puts it into a new pivot table. The code works every other

time that I run it, which is strange. Every other time that I run it,
I get a run-time
error #91. I ran into a similar problem
some time ago that I was able to fix by setting object variables to
nothing at the end of my code, however that doesn't seem to work here.
Below is the code:

Dim xlWb As Object
Dim xlWs As Object
Dim xlAp As Object


Set xlWb = ActiveWorkbook
Set xlWs = xlWb.Sheets("Sheet3")


a = xlWs.Range("A1").Address
lastcell = xlWs.Range("A1").SpecialCells(xlCellTypeLastCell).Address
MyRange = xlWs.Name & "!" & a & ":" & lastcell


With xlWb
'Add Table & cross fingers
.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=MyRange) _
.CreatePivotTable TableDestination:="", TableName:="PivotTable1"
End With


'Move desired column headings to correct sections on pivot table report

With ActiveSheet.PivotTables("PivotTable1").PivotFields(strField)
.Orientation = xlRowField
.Position = 1
End With


With ActiveSheet.PivotTables("PivotTable1").PivotFields(strField2)
.Orientation = xlColumnField
.Position = 1
End With


With ActiveSheet.PivotTables("PivotTable1").PivotFields(strField3)
.Orientation = xlDataField
.Position = 1
.Function = xlSum
End With


'Close Pivot Table Menus
ActiveWorkbook.ShowPivotTableFieldList = False
Application.CommandBars("PivotTable").Visible = False


'release excel objects after all activity
'******
Set xlWs = Nothing
Set xlWb = Nothing
Set objXl = Nothing
Set objSht = Nothing


....one other bit of info since I didn't include all of the code. The
variables 'strField, strField2, strField3' are defined as follows:


strField = objXl.Selection.Cells(1, 1).Text
strField2 = objXl.Selection.Cells(1, 7).Text
strField3 = objXl.Selection.Cells(1, 6).Text


....where 'objXl' is a New Excel.Application


I think this may be a problem area even though I'm setting it to =
nothing at the end of the code, but heck, I don't know. Any ideas as
to why this works exactly 1/2 of the time would be greatly appreciated.

 

[Tom Ogilvy]

strField = objXl.Selection.Cells(1, 1).Text
strField2 = objXl.Selection.Cells(1, 7).Text
strField3 = objXl.Selection.Cells(1, 6).Text

depends on what the activesheet is. Perhaps every other time, the correct
sheet is active when you run the code and the other times it isn't. (or
more subtle, maybe it is active, but sometimes you have a shape or button
selected when you run it although it should then error on the assignment -
you never say where it errors).

Why not use a specific sheet reference.

strFile = objXl.ActiveWorkbook.Worksheets("Sheet3").Cells(1,1).Text

perhaps.