Select a PivotField range without using the range object
I am trying to update the grouping of some dates in a pivottable using VBA.
Right now I select the pivotfield by using the range object (Activesheet.Range("B9").select). I would like to do this same process but by using the PivotField object instead (in case the location of the pivot field changes, user inserts/deletes rows, etc, etc). My code is below: For Each pf In pt.PivotFields 'look for TX_Date If pf.Name = "TX_Date" Then 'found it pf.Orientation = xlRowField 'move to to the row field 'select tx_date *****CHANGE THIS SO IT ISN'T STATIC***** ActiveSheet.Range("B9").Select Set rng = Selection 'slap it in my range variable 'group it rng.Group Start:=vntSt, End:=vntFin, periods:=Array(False, False, False, False, True, False, False) pf.Orientation = xlPageField 'move it back to the page field For Each pi In pf.PivotItems 'go thru all the values in TX_Date If InStr(pi.Name, "<") < 0 Or InStr(pi.Name, "") < 0 Then pi.Visible = False 'if its less than or greater than, don't show it End If Next pi Exit For 'only 1 TX_Date in pivot, we're done End If Next pf Set rng = Nothing Thanks! |
Select a PivotField range without using the range object
Never mind - answered my own question. For anyone who cares to know how I
got around the issue --- in the code below, just replace "ActiveSheet.Range("B9").Select" with the following: 'go thru entire pivot field data range (includes 2 pivot fields) For Each cell In pf.DataRange If cell.PivotCell.PivotField.Name = "TX_Date" Then 'found a value in TX_Date cell.Select 'select it Exit For 'we're done End If Next cell "Mike" wrote: I am trying to update the grouping of some dates in a pivottable using VBA. Right now I select the pivotfield by using the range object (Activesheet.Range("B9").select). I would like to do this same process but by using the PivotField object instead (in case the location of the pivot field changes, user inserts/deletes rows, etc, etc). My code is below: For Each pf In pt.PivotFields 'look for TX_Date If pf.Name = "TX_Date" Then 'found it pf.Orientation = xlRowField 'move to to the row field 'select tx_date *****CHANGE THIS SO IT ISN'T STATIC***** ActiveSheet.Range("B9").Select Set rng = Selection 'slap it in my range variable 'group it rng.Group Start:=vntSt, End:=vntFin, periods:=Array(False, False, False, False, True, False, False) pf.Orientation = xlPageField 'move it back to the page field For Each pi In pf.PivotItems 'go thru all the values in TX_Date If InStr(pi.Name, "<") < 0 Or InStr(pi.Name, "") < 0 Then pi.Visible = False 'if its less than or greater than, don't show it End If Next pi Exit For 'only 1 TX_Date in pivot, we're done End If Next pf Set rng = Nothing Thanks! |
All times are GMT +1. The time now is 12:00 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com