Just curious Tom...

In setting up the unique list of ITB and IO codes, what formula did you use?
I used a one formula
=IF(COUNTIF(MasterData!$K$2:I4,MasterData!I4)=1,Ma sterData!I4,"")
to pick out unique values, but it left blanks in between unique values. I
then used an array formula
{=NOBLANKS_1(L2:L20000)}
coupled with VBA to get rid of the blanks. It's made my worksheet rather
unwiedy... can you suggest a more efficient method? Thanks.
--
Kent Lysell
Financial Consultant
Ottawa, Ontario
W: 613.948-9557


"Tom Ogilvy" wrote:

I looked at your sheet and the three fields which meet your description are
called page fields - at least that is how they are refered to in the object
model and everywhere else I have seen them refered to.

they are
Source of Funds
ITBGrp
IO_Grp

The last two are what the macro is addressing using the pagefield object. I
fixed a glitch in the macro and it did exactly what I expected.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim pi As PivotItem
Dim sh As Worksheet
If Target.Count 1 Then Exit Sub
If Len(Trim(Target)) = 0 Then Exit Sub
If Target.Column = 3 Or Target.Column = 4 Then

Worksheets("PIV_Template").Copy _
After:=Worksheets(Worksheets.Count)
Set sh = ActiveSheet
sh.Name = Target
If Target.Column = 3 Then
With sh.PivotTables("Project_View") _
.PivotFields("ITBGrp")
For Each pi In .PivotItems
If LCase(pi.Value) = LCase(Target.Value) Then
.CurrentPage = pi.Value
End If
Next
End With
ElseIf Target.Column = 4 Then
With sh.PivotTables("Project_View") _
.PivotFields("IO_Grp")
For Each pi In .PivotItems
If LCase(pi.Value) = LCase(Target.Value) Then
.CurrentPage = pi.Value
End If
Next
End With
End If
sh.Activate
End If
End Sub


If you want something different, let me know. I had to set up a unique list
of ITBGrp and IO_Grp names (Unique_ITB_Grps, Unique_IO_Grps) to feed the data
validation dropdowns as they were broken in the file you sent me. After I
fixed those and fixed the glitch in the code, again, it worked as I expected.


--
Regards,
Tom Ogilvy





"klysell" wrote:

Hi Tom,

A page filter is the pivot field at the top, left-hand corner of pivot
table. It is a field physically apart from the main pivot table area, and tt
enables the user to choose the entire "theme" of the pivot table. Whereas, a
pivot field is either a column or row header.

Kent.
--
Kent Lysell
Financial Consultant
Ottawa, Ontario
W: 613.948-9557


"Tom Ogilvy" wrote:

I am not sure what a page filter is with respect to a pivot table. Could you
elaborate or send me a sample workbook with an explanation



--
regards,
Tom Ogilvy


"klysell" wrote:

Hi Tom,

I tried your code and I think it needs to be adjusted so that "page filters"
and not "page fields" are being updated from my list in the Summary sheet
(contained in Columns C and D). I tried to update it, but it didn't work. I
created a master pivot table with the desired format and the link to the data
and named it "PIV_Template". Here is the code that I have so far adapted from
your original code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim pi As PivotItem
If Target.Count 1 Then Exit Sub
If Target.Column = 3 Or Target.Column = 4 Then
Sheets("PIV_Template").Copy After:=Worksheets(Worksheets.Count)

If Target.Column = 3 Then
With sh.PivotTables("Project_View") _
.PageFields("ITBGrp")
For Each pi In .PivotItems
If LCase(pi.Value) = LCase(Target.Value) Then
.CurrentPage = pi.Value
End If
Next
End With
ElseIf Target.Column = 4 Then
With sh.PivotTables("Project_View") _
.PageFields("IO_Grp")
For Each pi In .PivotItems
If LCase(pi.Value) = LCase(Target.Value) Then
.CurrentPage = pi.Value
End If
Next
End With
End If
sh.Activate
End If
End Sub

Thanks a million.
--
Kent Lysell
Financial Consultant
Ottawa, Ontario
W: 613.948-9557