I agree on the sheet name and any other error handling you think is
appropriate. How you run the workbook is pretty much your call as I have no
idea how it will be used.

By the way, I got the second workbook also, but I had already fixed the
names.

--
Regards,
Tom Ogilvy


"klysell" wrote:

Thanks Tom.

As usual, you are the man! It works beautifully... I want to add some error
handling in case a user adds another code that has already been entered to
avoid the error message when there are duplicate sheet names, 31-character
sheet names, invalid charaters in sheet names, etc.

Also, I was thinking about having the user add all the codes that he/she
desires and only have them parsed into separate worksheets subsequent to a
macro being enacted. This would be in contrast to how it is now: each sheet
being added when one more code is selected (in an incremental fashion). What
do you think?

Many thanks,
Kent.
--
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