List pivot table page field dropdown list

[frapuano]

Hi


I have the necessity to list the content of the Page field dropdown list in a Pivot table .

To do this I have used the approach described in the following fragment of code ( in this case the page field is named Account Manager ):


Worksheets("SAM Report").Select

Range("CV12:CV100").ClearContents


Set pvtTable = Range("B6").PivotTable

rw = 11

For Each pvtitem In pvtTable.PivotFields("Account Manager").PivotItems

rw = rw + 1

Cells(rw, 100).Value = pvtitem.Name

Next


everythings looks OK ..but ..the content of this list is much bigger than the one that comes up when I click on the page dropdown list .

Takein consideration that I have not hidden any item from this list .

Probably is something linked to the Pivot cache but till now ( and are several days of analysis) I haven't suceeded to fix this issue or to find a workaround .

What I need is an exact matching between the 2 lists.


Thanks in advance for any suggestion , best regards


Francesco

 

[Hui]

Frapuano


I can't replicate your problem


Have you deleted the pivot table and started again, save the file inbetween


Also try a Ctrl Alt F9, that forces Excel to rebuild all the internal links for the file and it will automatically save after it has been done.


The following works ok for me


Sub test()

Worksheets("Sheet1").Select

Range("J1:J100").ClearContents


Set pvtTable = Range("F22").PivotTable

rw = 1

For Each pvtitem In pvtTable.PivotFields("Yr").PivotItems

rw = rw + 1

Cells(rw, 10).Value = pvtitem.Name

Next


End sub


But it was a fresh spreadsheet with new data