|
(previous discussion with Tom - Dynamically creating pt from li
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 |
(previous discussion with Tom - Dynamically creating pt fro
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 |
(previous discussion with Tom - Dynamically creating pt from li
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 |
(previous discussion with Tom - Dynamically creating pt fro
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 |
(previous discussion with Tom - Dynamically creating pt fro
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 |
(previous discussion with Tom - Dynamically creating pt fro
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 |
(previous discussion with Tom - Dynamically creating pt fro
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 |
(previous discussion with Tom - Dynamically creating pt fro
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 |
| All times are GMT +1. The time now is 08:11 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com