Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
CubeFields
I am trying to make a PivotTable change the selected field using the code
below. The part in PIVOTTABLE1 below works fine when I hard code the field names in the code but will not work when I try to reference the field names. When I run the PIVOTTABLE2 part of the code below I get a "Run-Time error '9' Subscript out of range." error. Does anyone know how to fix this so I can make the CubeFields reference a range name rather than me hard coding it? In the code below "curvar_h" and "selvar_h" are range names within my spreadsheet. __________________________________________________ ___________________ Private Sub ComboBox1_Change() 'Change PivotTable Variable - Detail Tables Dim hidevar_h Dim newvar_h hidevar_h = Evaluate("curvar_h").Value newvar_h = Evaluate("selvar_h").Value ActiveSheet.PivotTables("PivotTable1").CubeFields( "[homeowner]"). _ Orientation = xlHidden With ActiveSheet.PivotTables("PivotTable1").CubeFields( "[lapse]") .Orientation = xlColumnField .Position = 1 End With ActiveSheet.PivotTables("PivotTable2").CubeFields( hidevar_h). _ Orientation = xlHidden With ActiveSheet.PivotTables("PivotaTable2").CubeFields (newvar_h) .Orientation = xlColumnField .Position = 1 End With End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|