Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table
Hello,
I have a pivot table where I analyze supplier performance. I have the supplier name in the Page field. How can I determine in VBA the list of suppliers contained in the field? I want to be able to give a person a drop down box with a list of suppliers where they can pick the supplier name. I know they are listed in the page field but I am putting the pivot table on a hidden, protected sheet so they can't change it. Thanks, Bill |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table
Bill
Here's a function that will return a collection of all items in the pagefield. It finds which column the pagefield uses and loops through that column adding unique entries to the collection. There's not a lot in the way of error checking in it, but it should give you a start. Function UniquePageFields(pt As PivotTable) As Collection Dim colUniques As Collection Dim pf As PivotField Dim lCol As Long Dim rCell As Range Dim rngSource As Range Set pf = pt.PageFields(1) Set rngSource = Range(Application.ConvertFormula(pt.SourceData, xlR1C1, xlA1)) lCol = Application.WorksheetFunction.Match(pf.SourceName, rngSource.Rows(1), False) Set colUniques = New Collection For Each rCell In rngSource.Columns(lCol).Cells On Error Resume Next colUniques.Add rCell.Text, rCell.Text On Error GoTo 0 Next rCell Set UniquePageFields = colUniques End Function Sub test() Dim vItm As Variant Dim colPages As Collection Set colPages = UniquePageFields(Sheet1.PivotTables(1)) For Each vItm In colPages Debug.Print colPages.Item(vItm) Next vItm End Sub -- Dick Kusleika Excel MVP Daily Dose of Excel www.dicks-blog.com Bill wrote: Hello, I have a pivot table where I analyze supplier performance. I have the supplier name in the Page field. How can I determine in VBA the list of suppliers contained in the field? I want to be able to give a person a drop down box with a list of suppliers where they can pick the supplier name. I know they are listed in the page field but I am putting the pivot table on a hidden, protected sheet so they can't change it. Thanks, Bill |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table
Or, if the source data is an Excel worksheet, you could use an Advanced
Filter to extract the unique items from the page field's column, and sort the results. Dick Kusleika wrote: Bill Here's a function that will return a collection of all items in the pagefield. It finds which column the pagefield uses and loops through that column adding unique entries to the collection. There's not a lot in the way of error checking in it, but it should give you a start. Function UniquePageFields(pt As PivotTable) As Collection Dim colUniques As Collection Dim pf As PivotField Dim lCol As Long Dim rCell As Range Dim rngSource As Range Set pf = pt.PageFields(1) Set rngSource = Range(Application.ConvertFormula(pt.SourceData, xlR1C1, xlA1)) lCol = Application.WorksheetFunction.Match(pf.SourceName, rngSource.Rows(1), False) Set colUniques = New Collection For Each rCell In rngSource.Columns(lCol).Cells On Error Resume Next colUniques.Add rCell.Text, rCell.Text On Error GoTo 0 Next rCell Set UniquePageFields = colUniques End Function Sub test() Dim vItm As Variant Dim colPages As Collection Set colPages = UniquePageFields(Sheet1.PivotTables(1)) For Each vItm In colPages Debug.Print colPages.Item(vItm) Next vItm End Sub -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to create pivot table from existing pivot table in excel 2007 | Excel Discussion (Misc queries) | |||
Print Pivot Table headers on pages with Pivot Table | Excel Worksheet Functions | |||
Filter lines with Pivot table and non pivot table columns | Charts and Charting in Excel | |||
Filter lines with Pivot table and non Pivot table columns | Excel Discussion (Misc queries) | |||
Help required with setting up a pivot table with the source on sheet1 to have the pivot table created on sheet called "report" | Excel Programming |