PivotTable.columnfields(index) vs Pivotcell.columnitem(index)

[Crypto]

I have a pivot table that looks like the following:
(Rowfields : Country, State
Columnfields: Company, Department)

Dell
Sales | Marketing
USA | CA 8 10
| FL 2 3

As I'm trying to automate the process of extracting data from this
table using GetPivotData method, I ran into a problem. I was puzzled
to find that PT.ColumnFields(1) returns "Department" not "Company,"
yet pcell.columnitems(1) returns "Dell", not "Sales." In other words,
columnitems use the index the items in the order they appear, but
columnfields do not. I think what's happening here is that
Columnfields index the items in the order added (to the pivottable),
regardless of its actual location it is placed relative to the other
column fields.
Apparently, this causes errors with GetPivotData because there is no
Department called Dell.

Set PT = Worksheets("test").PivotTables(1)
Set pcell = Activecell.PivotCell

PT.GetPivotData(pcell.DataField, PT.RowFields(1), pcell.RowItems(1), _
PT.RowFields(2),
pcell.RowItems(2), _
PT.ColumnFields(1),
pcell.ColumnItems(1), _
PT.ColumnFields(2),
pcell.ColumnItems(2))

With that said, is there a way to get the field name based on the
actual location?

Thanks for your help in advance.

 

[Roger Govier]

Hi Jason

ColumnFields and RowFields return the values in the order of the columns
in the source data, not however they are arranged on the PT.

In Cells G1 and H1 of my sheet I set up Sales and Marketing. In F2 and
F3 I set up CA and FL

The following code looped through and filled the cells for me with data
from the PT.

Sub test()
Dim PT As PivotTable, i As Long, j As Long
Set PT = Worksheets("Sheet2").PivotTables("Pivottable3")
For i = 2 To 3
For j = 7 To 8

Cells(i, j) = PT.GetPivotData(PT.ColumnFields(1), _
PT.RowFields(1), "US", _
PT.RowFields(2), Cells(i, 6), _ 'State
PT.ColumnFields(1), Cells(1, j), _ 'Department
PT.ColumnFields(2), "Dell")
Next j
Next i
End Sub

Clearly the Values for US and Dell could have been set to be read from
other variables if required.

 

[Crypto]

Thanks so very much for taking the time to reply to my question.
In the line where it says Cells(i, j) =
PT.GetPivotData(PT.ColumnFields(1), _
Did you mean "pcell.datafield"? It issues an error with the
PT.Columnfields(1)

Also I wonder how the worksheet function GetPivotData automatically
pick up the matching columnfield and columnitem when you click in the
pivot table. If GetPivotData uses the same objects and properties that
are available in VBA, it means there is a way to know the columnfield
name (eg. Company) corresponding to a certain columnitem. (eg. Dell)

 

[Roger Govier]

Hi jason

In my PT source table, I had Country, State, Department, Company in that
order.
In the PT I have Country, then State as row Items
Department then Company as Column items
My Data field is Count of Department.

PT.ColumnFields(1) returns Department for me, which is correct.
The code runs fine for me, using XL2003

If you were using Count of State as your data field, then you would need
to amend the code to

Cells(i, j) = PT.GetPivotData(PT.RowFields(2), _
PT.RowFields(1), "us", _
PT.RowFields(2), Cells(i, 6), _
PT.ColumnFields(1), Cells(1, j), _
PT.ColumnFields(2), "Dell")

Because Data is being obtained from a Row field, and it is the second
row field.

Similarly, if there were a 5th column titled Value, with numbers like
20, 30, 40 etc. and the Sum of Values was what was added to the data
area, then it would be

Cells(i, j) = PT.GetPivotData(PT.DataFields(1), _
PT.RowFields(1), "us", _
PT.RowFields(2), Cells(i, 6), _
PT.ColumnFields(1), Cells(1, j), _
PT.ColumnFields(2), "Dell")

 

[Crypto]

Hi jason

In my PT source table, I had Country, State, Department, Company in that
order.
In the PT I have Country, then State as row Items
Department then Company as Column items
My Data field is Count of Department.

PT.ColumnFields(1) returns Department for me, which is correct.
The code runs fine for me, using XL2003

If you were using Count of State as your data field, then you would need
to amend the code to

Cells(i, j) = PT.GetPivotData(PT.RowFields(2), _
PT.RowFields(1), "us", _
PT.RowFields(2), Cells(i, 6), _
PT.ColumnFields(1), Cells(1, j), _
PT.ColumnFields(2), "Dell")

Because Data is being obtained from a Row field, and it is the second
row field.

Similarly, if there were a 5th column titled Value, with numbers like
20, 30, 40 etc. and the Sum of Values was what was added to the data
area, then it would be

Cells(i, j) = PT.GetPivotData(PT.DataFields(1), _
PT.RowFields(1), "us", _
PT.RowFields(2), Cells(i, 6), _
PT.ColumnFields(1), Cells(1, j), _
PT.ColumnFields(2), "Dell")

Thanks again for your kind help!