I've got a macro that for a selected agency will find the city/state
associated with that agency and then chooses that state in a pivot table.

For some reason things are no longer working. The macro seems to choose the
state in the pivot table, but the pivot table then shows no data. If I
manually select a different state the pivot table will populate with data and
then I can manually re-select the correct state using the drop down menu and
it will also work. It just doesn't seem to work when I have the macro select
it.

Any ideas on what I'm doing wrong or how I can fix this?
It doesn't appear to be a trailing space issue, fwiw.

You probably corrupted your pivot table using your method of assignment. I
have found it best to actually use the value of the pivotitem to make the
assignment. Try recreating the pivot table to get rid of the "corruption"
and then use somthing like
this example:

With sh.PivotTables("Project_View") _
..PivotFields("ITBGrp")
For Each pi In .PivotItems
If LCase(pi.Value) = LCase(Range("B9").Value) Then
..CurrentPage = pi.Value
End If
Next
End With

where I am setting the pagefield to the value in cell B9.

--
Regards,
Tom Ogilvy



"Allen" wrote:

I've got a macro that for a selected agency will find the city/state
associated with that agency and then chooses that state in a pivot table.

For some reason things are no longer working. The macro seems to choose the
state in the pivot table, but the pivot table then shows no data. If I
manually select a different state the pivot table will populate with data and
then I can manually re-select the correct state using the drop down menu and
it will also work. It just doesn't seem to work when I have the macro select
it.

Any ideas on what I'm doing wrong or how I can fix this?
It doesn't appear to be a trailing space issue, fwiw.

On Jul 17, 7:54 am, Allen wrote:
I've got a macro that for a selected agency will find the city/state
associated with that agency and then chooses that state in a pivot table.

For some reason things are no longer working. The macro seems to choose the
state in the pivot table, but the pivot table then shows no data. If I
manually select a different state the pivot table will populate with data and
then I can manually re-select the correct state using the drop down menu and
it will also work. It just doesn't seem to work when I have the macro select
it.

Any ideas on what I'm doing wrong or how I can fix this?
It doesn't appear to be a trailing space issue, fwiw.

It is hard to tell what is wrong with something like this without
seeing the section of code that you think should be working. I do
this with several tables. I have pivot tables that cycle through all
the page fields and format the reports onto separate sheets.

Peter