The other thing that I should mention is that you can quite often just use a SUMPRODUCT formula to
return the value of interest, instead of the pivot table. It is a little easier to setup, if you are
just looking for a few values.

HTH,
Bernie
MS Excel MVP


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
You can change what a pivot table shows easily in code, but just make sure that you set all the
items visible prior to hiding other items, since at least one item need to be visible or your code
will error out.

For example, witht he value that you want to show in Worksheets("Sheet1").Range("A1")

Sub Macro2()
Dim myItem As PivotItem

For Each myItem In Worksheets("Sheet3").PivotTables("PivotTable2").Pi votFields("name").PivotItems
myItem.Visible = True
Next myItem

For Each myItem In Worksheets("Sheet3").PivotTables("PivotTable2").Pi votFields("name").PivotItems
If myItem.Name < Worksheets("Sheet1").Range("A1").Value Then
myItem.Visible = False
End If
Next myItem

End Sub

You can, of course, use appropriate objects for the worksheets, pivot tables, etc.

You can update the pivot table, and then use GETPIVOTDATA formulas to extract the desired values

See this page for how to form the formulas:

http://www.contextures.com/xlPivot06.html


HTH,
Bernie
MS Excel MVP


"Ray" wrote in message
...
Hi Bernie -

Thanks for responding ... you're correct, it can certainly be done
that way. My 'hesitation' is that many (possibly most) of the users
aren't really that comfortable with Excel, so I'm attempting to make
it as un-Excel like as possible. All user-interaction will be through
userforms ...

Wait, could I have the pivot-table be created/updated 'behind the
scenes' (on a hidden worksheet) and then show the results in the
listbox? I assume I'd need a named range for the pivot-table -- how
is that done?

this is interesting ... I'd have numerous applications for this!

thanks again,
ray