Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Visual Basic Error Run Time Error, Type Mismatch | Excel Discussion (Misc queries) | |||
Conditional Formatting - Run Time Error '13' Type Mismatch Error | Excel Programming | |||
run-time error '1004': Application-defined or object-deifined error | Excel Programming | |||
Befuddled with For Next Loop ------ Run - Time Error '13' Type Mismatch Error | Excel Programming | |||
Code Error - Run Time Error 5 (Disable Cut, Copy & Paste) | Excel Programming |