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

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

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

Tom,

In XL XP (IIRC), a page field was added, sort of like using a primary row field that allowed you to
show just one item (but you can only show one, not select which ones with check boxes like you can
in a regular row field).

In the Pivot Table Field List dialog, there is an "Add to" section, and you can select "Page Area"
in that selection box.

Bernie


"Tom Ogilvy" wrote in message
...
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

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

Bernie

thanks. It appears you are also describing pagefields. But I have been
using them since xl97 and they may have been there earlier.

--
Regards,
Tom Ogilvy

"Bernie Deitrick" wrote:

Tom,

In XL XP (IIRC), a page field was added, sort of like using a primary row field that allowed you to
show just one item (but you can only show one, not select which ones with check boxes like you can
in a regular row field).

In the Pivot Table Field List dialog, there is an "Add to" section, and you can select "Page Area"
in that selection box.

Bernie


"Tom Ogilvy" wrote in message
...
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

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

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

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