Navigate Pivot Table Row Structure

abbeyWigan

New Member
Joined
Dec 7, 2007
Messages
42
Does anyone know how best to navigate a pivot table sideways (i.e. on same row but from column to column) using VBA?

If I pick any pivot item in the pivot table, I can find its parent item or column heading within the same column by simply doing

Code:
Set obj = pivotItem.Paent

However, I've been pulling my hair out trying to find a function or method that will allow me to navigate sideways, i.e. return the corresponding value (or range object) at the same level from another column.

Code:
pivotItem.DrillTo()
seems to be a natural candidate but I can't get it to work. GetPivotData doesn't seem to work for me either. I'm having a really bad day on this.

My pivot table is formatted to in tabular mode with column headings and collapsible rows. All I want to do is be able to pick any sub pivot item and get its left-most parent on the same row.
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
4,090
Hi


Code:
Sub Sideways()
Dim pt As PivotTable, pi As PivotItem, pia$
Set pt = ActiveSheet.PivotTables("pivottable4")
Set pi = pt.PivotFields("values").PivotItems(2)                 ' desired pivot item
pia = pi.DataRange.Cells(1, 1).Row                              ' desired row
MsgBox Cells(pia, pt.DataBodyRange.Columns(1).Column)
MsgBox Cells(pia, pt.RowRange.Columns(1).Column)
End Sub
 

abbeyWigan

New Member
Joined
Dec 7, 2007
Messages
42
Hi


Code:
Sub Sideways()
Dim pt As PivotTable, pi As PivotItem, pia$
Set pt = ActiveSheet.PivotTables("pivottable4")
Set pi = pt.PivotFields("values").PivotItems(2)                 ' desired pivot item
pia = pi.DataRange.Cells(1, 1).Row                              ' desired row
MsgBox Cells(pia, pt.DataBodyRange.Columns(1).Column)
MsgBox Cells(pia, pt.RowRange.Columns(1).Column)
End Sub

Many thanks Worf. I'll try that and let you know how I get on.
 

abbeyWigan

New Member
Joined
Dec 7, 2007
Messages
42
Hi Worf, I've tried it unfortunately it fails with the same error message I was getting with DataRange. When it gets to

pia = pi.DataRange.Cells(1, 1).Row

it throws "Run-time error '1004': Unable to get the DataRange property of the PivotItem class"
 

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
4,090
o Sorry for the late reply.
o Did you change names and indexes to suit your table?
o Can you post a link to a workbook containing a sample table that throws the error?
 

abbeyWigan

New Member
Joined
Dec 7, 2007
Messages
42
o Sorry for the late reply.
o Did you change names and indexes to suit your table?
o Can you post a link to a workbook containing a sample table that throws the error?


I've played around and solved the problem. Section of code (with offending line highlighted) posted below. The problem is DataRange property seems to only exist for pivot items that are actually displayed on the screen. This is unlike normal range objects that you can continue to manipulate even when hidden. This means even if the ShowDetail property of the pivot item is set, if the screen is not refreshed to display it, DataRange fails. Through judicious use of ScreenUpdating I have managed to work around this very annoying issue by writing a method to make sure all pivot table fields are expanded and visible then restore all settings back to what it was after the code section above has finished. Slower but it works.

Many thanks for your help.

Code:
For x = 0 To UBound(tmp) 'Iterate each field and
    With pt.PivotFields(tmp(x)) 'count the number
        rCnt = 0
        For Each pi In .PivotItems 'of TCR's in each...
            isTc = IsTCR(pi, NoMsg:=True)
            If isTc Then
                cnts(x) = cnts(x) + 1 'Update count

                [u][I][b]Set rng = pi.DataRange: Set rng = rng.Parent.Range("A" & rng.Row)[/b][/I][/u]

                fnd = FindItem(rng.Text, nms(x)) 'See if company name exists.
                If Not fnd Then nms(x) = nms(x) & "," & rng.Text 'If not, add it.
                Set lfm(x) = xUnion(lfm(x), rng)
            End If
        Next pi
    End With
Next x
 

Watch MrExcel Video

Forum statistics

Threads
1,122,158
Messages
5,594,585
Members
413,915
Latest member
namreh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top