Hi All,

I am having problems with selecting a page field in a number of pivot
tables using VBA.

I have 10 pivot tables in separate sheets. Each is looking at the same
data, and all have the same layout. Each one has a Page field showing
months and row headings showing dates. They all also have another Page
field which will not change, and the value in each of these is
different for each pivot.

I want the user to be able to select a month from a dropdown or list
box, either in a userform or embedded on a sheet and for this to
change the month in all the pivot tables. The rows will then change to
show only dates from that month. This code works fine until the user
selects a month for which there is no data. My code just adds the
month as a new Page field and leaves the previously selected data
showing. So, for example, if the user selects September, and there is
no September data, you get September as a Page Field and August dates
in the row headings, if August was the previously selected month.

I then thought that if I restricted the dropdown/list box to show only
items in the page field, the user would not be able to select a
non-existent month. I found a bit of code in this group but it loads
not only the months in the Page field but also the dates I have as row
headings.

My code to change the page field is:

'Loop through all sheets with pivottables and do this
Sheets(SheetName).PivotTables(PivotTableName).Pivo tFields("FiscalMonth").CurrentPage
= Me.ComboBox1.Value

My code to add months to the dropdown is: (From Bill Glanville)

Dim PI As PivotItem
Me.ComboBox1.Clear
For Each PI In Sheets("Sheet1").PivotTables("PivotTable1").PageFi elds("FiscalMonth").PivotItems
Me.ComboBox1.AddItem PI.Name
Next

Any help, as always, much appreciated.