(previous discussion with Tom - re:Dynamically creating pt from li

[Guest]

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.

 

[Guest]

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

(e-mail address removed)

 

[Guest]

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.

 

[Bernie Deitrick]

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

 

[Guest]

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

 

[Guest]

Bernie

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

 

[Guest]

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.

 

[Guest]

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.

 

[Guest]

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,MasterData!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.