Run-Time Error 91
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. 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. |
All times are GMT +1. The time now is 01:55 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com