|
(repost) Dynamically creating pivot tables from page-field list
Hi,
I have two adjacent columns, occuping Column C and D (range C15:D100), in my summary sheet. The user selects values from a drop-down list in both columns, with one OR the other value in a pair being selected (and the other remaining blank). I would like to dynamically generate pivot tables based on data from my "PivotData" sheet, according to a pre-arranged PT format, with either the value in Column C OR Column D forming the Pivot table page filter. Essentially I'd like a macro to create new worksheets, each with a new pivot table using a page filter from one or the other value of user-chosen, drop-down values in Column C or D. Perhaps once the user has selected values in Column C and D (e.g. for row 15, either C15 or D15 is filled with data using a data validation function), a macro will parse these values in separate worksheets and a pivot table is generated using this values in Columns C and D as page filters. I already have a pivot table designed, and I know that I could turn on the macro recorder and use this code when dynamically generating all the pivot tables. I could then refine this recorded macro to be more efficient (i.e. using knowledge gained from John Walkenbach's Power Programming with VBA book). I just need the VBA code that will do the parsing of these values. What would be my best route to solve this problem? Thanks in advance, -- Kent Lysell Financial Consultant Ottawa, Ontario W: 613.948-9557 |
(repost) Dynamically creating pivot tables from page-field list
Seems like I have seen this question posted before. If you didn't get a
response, it generally means that the question isn't understood (or less likely, it is not doable or to complex to attempt an answer (someone wants a free project done for them)). I would guess your question is not understood. For me: So the question you ask is: I just need the VBA code that will do the parsing of these values What do you mean by "these values"? What do you mean by parsing? -- Regards, Tom Ogilvy "klysell" wrote: Hi, I have two adjacent columns, occuping Column C and D (range C15:D100), in my summary sheet. The user selects values from a drop-down list in both columns, with one OR the other value in a pair being selected (and the other remaining blank). I would like to dynamically generate pivot tables based on data from my "PivotData" sheet, according to a pre-arranged PT format, with either the value in Column C OR Column D forming the Pivot table page filter. Essentially I'd like a macro to create new worksheets, each with a new pivot table using a page filter from one or the other value of user-chosen, drop-down values in Column C or D. Perhaps once the user has selected values in Column C and D (e.g. for row 15, either C15 or D15 is filled with data using a data validation function), a macro will parse these values in separate worksheets and a pivot table is generated using this values in Columns C and D as page filters. I already have a pivot table designed, and I know that I could turn on the macro recorder and use this code when dynamically generating all the pivot tables. I could then refine this recorded macro to be more efficient (i.e. using knowledge gained from John Walkenbach's Power Programming with VBA book). I just need the VBA code that will do the parsing of these values. What would be my best route to solve this problem? Thanks in advance, -- Kent Lysell Financial Consultant Ottawa, Ontario W: 613.948-9557 |
(repost) Dynamically creating pivot tables from page-field lis
H Tom,
Well, I also posted a similar question a number of months ago when I wanted to parse employ names from a column in my summary sheet to individually macro-generated worksheets. This worked fine. Now I have another project, and I am using the same principle, but this time, I need to parse a list of codes from Column C into individual worksheets. The only difference is that I each worksheet is a pivot table whose page filter is one of the values in my Column C. I'm thinking that this might be too complex. Instead, perhaps I can pre-configure pivot tables and have their page filter values linked to drop-down selections on my Summary Sheet. Perhaps a radial button can unhide a pivot table when the user has selected a value for its page filter.... What do you think? -- Kent Lysell Financial Consultant Ottawa, Ontario W: 613.948-9557 "Tom Ogilvy" wrote: Seems like I have seen this question posted before. If you didn't get a response, it generally means that the question isn't understood (or less likely, it is not doable or to complex to attempt an answer (someone wants a free project done for them)). I would guess your question is not understood. For me: So the question you ask is: I just need the VBA code that will do the parsing of these values What do you mean by "these values"? What do you mean by parsing? -- Regards, Tom Ogilvy "klysell" wrote: Hi, I have two adjacent columns, occuping Column C and D (range C15:D100), in my summary sheet. The user selects values from a drop-down list in both columns, with one OR the other value in a pair being selected (and the other remaining blank). I would like to dynamically generate pivot tables based on data from my "PivotData" sheet, according to a pre-arranged PT format, with either the value in Column C OR Column D forming the Pivot table page filter. Essentially I'd like a macro to create new worksheets, each with a new pivot table using a page filter from one or the other value of user-chosen, drop-down values in Column C or D. Perhaps once the user has selected values in Column C and D (e.g. for row 15, either C15 or D15 is filled with data using a data validation function), a macro will parse these values in separate worksheets and a pivot table is generated using this values in Columns C and D as page filters. I already have a pivot table designed, and I know that I could turn on the macro recorder and use this code when dynamically generating all the pivot tables. I could then refine this recorded macro to be more efficient (i.e. using knowledge gained from John Walkenbach's Power Programming with VBA book). I just need the VBA code that will do the parsing of these values. What would be my best route to solve this problem? Thanks in advance, -- Kent Lysell Financial Consultant Ottawa, Ontario W: 613.948-9557 |
(repost) Dynamically creating pivot tables from page-field lis
By the way, I use the term "parse" to mean to create a worksheet with a pivot
table that uses one of the values in column c on my Summary Sheet. -- Kent Lysell Financial Consultant Ottawa, Ontario W: 613.948-9557 "klysell" wrote: H Tom, Well, I also posted a similar question a number of months ago when I wanted to parse employ names from a column in my summary sheet to individually macro-generated worksheets. This worked fine. Now I have another project, and I am using the same principle, but this time, I need to parse a list of codes from Column C into individual worksheets. The only difference is that I each worksheet is a pivot table whose page filter is one of the values in my Column C. I'm thinking that this might be too complex. Instead, perhaps I can pre-configure pivot tables and have their page filter values linked to drop-down selections on my Summary Sheet. Perhaps a radial button can unhide a pivot table when the user has selected a value for its page filter.... What do you think? -- Kent Lysell Financial Consultant Ottawa, Ontario W: 613.948-9557 "Tom Ogilvy" wrote: Seems like I have seen this question posted before. If you didn't get a response, it generally means that the question isn't understood (or less likely, it is not doable or to complex to attempt an answer (someone wants a free project done for them)). I would guess your question is not understood. For me: So the question you ask is: I just need the VBA code that will do the parsing of these values What do you mean by "these values"? What do you mean by parsing? -- Regards, Tom Ogilvy "klysell" wrote: Hi, I have two adjacent columns, occuping Column C and D (range C15:D100), in my summary sheet. The user selects values from a drop-down list in both columns, with one OR the other value in a pair being selected (and the other remaining blank). I would like to dynamically generate pivot tables based on data from my "PivotData" sheet, according to a pre-arranged PT format, with either the value in Column C OR Column D forming the Pivot table page filter. Essentially I'd like a macro to create new worksheets, each with a new pivot table using a page filter from one or the other value of user-chosen, drop-down values in Column C or D. Perhaps once the user has selected values in Column C and D (e.g. for row 15, either C15 or D15 is filled with data using a data validation function), a macro will parse these values in separate worksheets and a pivot table is generated using this values in Columns C and D as page filters. I already have a pivot table designed, and I know that I could turn on the macro recorder and use this code when dynamically generating all the pivot tables. I could then refine this recorded macro to be more efficient (i.e. using knowledge gained from John Walkenbach's Power Programming with VBA book). I just need the VBA code that will do the parsing of these values. What would be my best route to solve this problem? Thanks in advance, -- Kent Lysell Financial Consultant Ottawa, Ontario W: 613.948-9557 |
(repost) Dynamically creating pivot tables from page-field lis
After rereading your original and then this one, I think the original sounds
more satisfying than a huge workbook with many sheets with pivot tables. Have a template pivot table already linked to your data. When a user selects a value from the dropdown on the summary sheet, then copy this pivot table template sheet and set the pagefilter. so you could use the change event of the summary sheet. Right click on the summary sheet tab and select view code. Put in code like this: 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 set worksheets("PIV_Template").Copy( After:=Worksheets(worksheets.count)) it Target.column = 3 then With sh.PivotTables("PivotTable1") _ .PivotFields("Name") 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("PivotTable1") _ .PivotFields("Company") 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 You would need to adjust it to match the pivot table name and the field names. The template pivot table would have page fields for both possible selections - or you could make the macro more extensive in rearranging the table. Of course I could have misunderstood. -- Regards, Tom Ogilvy "klysell" wrote: H Tom, Well, I also posted a similar question a number of months ago when I wanted to parse employ names from a column in my summary sheet to individually macro-generated worksheets. This worked fine. Now I have another project, and I am using the same principle, but this time, I need to parse a list of codes from Column C into individual worksheets. The only difference is that I each worksheet is a pivot table whose page filter is one of the values in my Column C. I'm thinking that this might be too complex. Instead, perhaps I can pre-configure pivot tables and have their page filter values linked to drop-down selections on my Summary Sheet. Perhaps a radial button can unhide a pivot table when the user has selected a value for its page filter.... What do you think? -- Kent Lysell Financial Consultant Ottawa, Ontario W: 613.948-9557 "Tom Ogilvy" wrote: Seems like I have seen this question posted before. If you didn't get a response, it generally means that the question isn't understood (or less likely, it is not doable or to complex to attempt an answer (someone wants a free project done for them)). I would guess your question is not understood. For me: So the question you ask is: I just need the VBA code that will do the parsing of these values What do you mean by "these values"? What do you mean by parsing? -- Regards, Tom Ogilvy "klysell" wrote: Hi, I have two adjacent columns, occuping Column C and D (range C15:D100), in my summary sheet. The user selects values from a drop-down list in both columns, with one OR the other value in a pair being selected (and the other remaining blank). I would like to dynamically generate pivot tables based on data from my "PivotData" sheet, according to a pre-arranged PT format, with either the value in Column C OR Column D forming the Pivot table page filter. Essentially I'd like a macro to create new worksheets, each with a new pivot table using a page filter from one or the other value of user-chosen, drop-down values in Column C or D. Perhaps once the user has selected values in Column C and D (e.g. for row 15, either C15 or D15 is filled with data using a data validation function), a macro will parse these values in separate worksheets and a pivot table is generated using this values in Columns C and D as page filters. I already have a pivot table designed, and I know that I could turn on the macro recorder and use this code when dynamically generating all the pivot tables. I could then refine this recorded macro to be more efficient (i.e. using knowledge gained from John Walkenbach's Power Programming with VBA book). I just need the VBA code that will do the parsing of these values. What would be my best route to solve this problem? Thanks in advance, -- Kent Lysell Financial Consultant Ottawa, Ontario W: 613.948-9557 |
(repost) Dynamically creating pivot tables from page-field lis
Hi Tom,
Thanks so much! I'll mull over what you sent me using my virgin-VBA understanding and see if I can integrate it. All the best, -- Kent Lysell Financial Consultant Ottawa, Ontario W: 613.948-9557 "Tom Ogilvy" wrote: After rereading your original and then this one, I think the original sounds more satisfying than a huge workbook with many sheets with pivot tables. Have a template pivot table already linked to your data. When a user selects a value from the dropdown on the summary sheet, then copy this pivot table template sheet and set the pagefilter. so you could use the change event of the summary sheet. Right click on the summary sheet tab and select view code. Put in code like this: 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 set worksheets("PIV_Template").Copy( After:=Worksheets(worksheets.count)) it Target.column = 3 then With sh.PivotTables("PivotTable1") _ .PivotFields("Name") 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("PivotTable1") _ .PivotFields("Company") 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 You would need to adjust it to match the pivot table name and the field names. The template pivot table would have page fields for both possible selections - or you could make the macro more extensive in rearranging the table. Of course I could have misunderstood. -- Regards, Tom Ogilvy "klysell" wrote: H Tom, Well, I also posted a similar question a number of months ago when I wanted to parse employ names from a column in my summary sheet to individually macro-generated worksheets. This worked fine. Now I have another project, and I am using the same principle, but this time, I need to parse a list of codes from Column C into individual worksheets. The only difference is that I each worksheet is a pivot table whose page filter is one of the values in my Column C. I'm thinking that this might be too complex. Instead, perhaps I can pre-configure pivot tables and have their page filter values linked to drop-down selections on my Summary Sheet. Perhaps a radial button can unhide a pivot table when the user has selected a value for its page filter.... What do you think? -- Kent Lysell Financial Consultant Ottawa, Ontario W: 613.948-9557 "Tom Ogilvy" wrote: Seems like I have seen this question posted before. If you didn't get a response, it generally means that the question isn't understood (or less likely, it is not doable or to complex to attempt an answer (someone wants a free project done for them)). I would guess your question is not understood. For me: So the question you ask is: I just need the VBA code that will do the parsing of these values What do you mean by "these values"? What do you mean by parsing? -- Regards, Tom Ogilvy "klysell" wrote: Hi, I have two adjacent columns, occuping Column C and D (range C15:D100), in my summary sheet. The user selects values from a drop-down list in both columns, with one OR the other value in a pair being selected (and the other remaining blank). I would like to dynamically generate pivot tables based on data from my "PivotData" sheet, according to a pre-arranged PT format, with either the value in Column C OR Column D forming the Pivot table page filter. Essentially I'd like a macro to create new worksheets, each with a new pivot table using a page filter from one or the other value of user-chosen, drop-down values in Column C or D. Perhaps once the user has selected values in Column C and D (e.g. for row 15, either C15 or D15 is filled with data using a data validation function), a macro will parse these values in separate worksheets and a pivot table is generated using this values in Columns C and D as page filters. I already have a pivot table designed, and I know that I could turn on the macro recorder and use this code when dynamically generating all the pivot tables. I could then refine this recorded macro to be more efficient (i.e. using knowledge gained from John Walkenbach's Power Programming with VBA book). I just need the VBA code that will do the parsing of these values. What would be my best route to solve this problem? Thanks in advance, -- Kent Lysell Financial Consultant Ottawa, Ontario W: 613.948-9557 |
(repost) Dynamically creating pivot tables from page-field lis
Tom,
Why is the code on the fifth line, "set worksheets("PIV_Template").Copy( After:=Worksheets(worksheets.count))" giving me a compile error (missing "=") TIA -- Kent Lysell Financial Consultant Ottawa, Ontario W: 613.948-9557 "Tom Ogilvy" wrote: After rereading your original and then this one, I think the original sounds more satisfying than a huge workbook with many sheets with pivot tables. Have a template pivot table already linked to your data. When a user selects a value from the dropdown on the summary sheet, then copy this pivot table template sheet and set the pagefilter. so you could use the change event of the summary sheet. Right click on the summary sheet tab and select view code. Put in code like this: 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 set worksheets("PIV_Template").Copy( After:=Worksheets(worksheets.count)) it Target.column = 3 then With sh.PivotTables("PivotTable1") _ .PivotFields("Name") 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("PivotTable1") _ .PivotFields("Company") 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 You would need to adjust it to match the pivot table name and the field names. The template pivot table would have page fields for both possible selections - or you could make the macro more extensive in rearranging the table. Of course I could have misunderstood. -- Regards, Tom Ogilvy "klysell" wrote: H Tom, Well, I also posted a similar question a number of months ago when I wanted to parse employ names from a column in my summary sheet to individually macro-generated worksheets. This worked fine. Now I have another project, and I am using the same principle, but this time, I need to parse a list of codes from Column C into individual worksheets. The only difference is that I each worksheet is a pivot table whose page filter is one of the values in my Column C. I'm thinking that this might be too complex. Instead, perhaps I can pre-configure pivot tables and have their page filter values linked to drop-down selections on my Summary Sheet. Perhaps a radial button can unhide a pivot table when the user has selected a value for its page filter.... What do you think? -- Kent Lysell Financial Consultant Ottawa, Ontario W: 613.948-9557 "Tom Ogilvy" wrote: Seems like I have seen this question posted before. If you didn't get a response, it generally means that the question isn't understood (or less likely, it is not doable or to complex to attempt an answer (someone wants a free project done for them)). I would guess your question is not understood. For me: So the question you ask is: I just need the VBA code that will do the parsing of these values What do you mean by "these values"? What do you mean by parsing? -- Regards, Tom Ogilvy "klysell" wrote: Hi, I have two adjacent columns, occuping Column C and D (range C15:D100), in my summary sheet. The user selects values from a drop-down list in both columns, with one OR the other value in a pair being selected (and the other remaining blank). I would like to dynamically generate pivot tables based on data from my "PivotData" sheet, according to a pre-arranged PT format, with either the value in Column C OR Column D forming the Pivot table page filter. Essentially I'd like a macro to create new worksheets, each with a new pivot table using a page filter from one or the other value of user-chosen, drop-down values in Column C or D. Perhaps once the user has selected values in Column C and D (e.g. for row 15, either C15 or D15 is filled with data using a data validation function), a macro will parse these values in separate worksheets and a pivot table is generated using this values in Columns C and D as page filters. I already have a pivot table designed, and I know that I could turn on the macro recorder and use this code when dynamically generating all the pivot tables. I could then refine this recorded macro to be more efficient (i.e. using knowledge gained from John Walkenbach's Power Programming with VBA book). I just need the VBA code that will do the parsing of these values. What would be my best route to solve this problem? Thanks in advance, -- Kent Lysell Financial Consultant Ottawa, Ontario W: 613.948-9557 |
(repost) Dynamically creating pivot tables from page-field lis
I used:
"Sheets("PIV_Template").Copy After:=Worksheets(Worksheets.Count)" in place of: "set worksheets("PIV_Template").Copy( After:=Worksheets(worksheets.count)) " and I no longer got the compile error message. Does this new code supplant the old code without changing the purpose? -- Kent Lysell Financial Consultant Ottawa, Ontario W: 613.948-9557 "Tom Ogilvy" wrote: After rereading your original and then this one, I think the original sounds more satisfying than a huge workbook with many sheets with pivot tables. Have a template pivot table already linked to your data. When a user selects a value from the dropdown on the summary sheet, then copy this pivot table template sheet and set the pagefilter. so you could use the change event of the summary sheet. Right click on the summary sheet tab and select view code. Put in code like this: 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 set worksheets("PIV_Template").Copy( After:=Worksheets(worksheets.count)) it Target.column = 3 then With sh.PivotTables("PivotTable1") _ .PivotFields("Name") 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("PivotTable1") _ .PivotFields("Company") 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 You would need to adjust it to match the pivot table name and the field names. The template pivot table would have page fields for both possible selections - or you could make the macro more extensive in rearranging the table. Of course I could have misunderstood. -- Regards, Tom Ogilvy "klysell" wrote: H Tom, Well, I also posted a similar question a number of months ago when I wanted to parse employ names from a column in my summary sheet to individually macro-generated worksheets. This worked fine. Now I have another project, and I am using the same principle, but this time, I need to parse a list of codes from Column C into individual worksheets. The only difference is that I each worksheet is a pivot table whose page filter is one of the values in my Column C. I'm thinking that this might be too complex. Instead, perhaps I can pre-configure pivot tables and have their page filter values linked to drop-down selections on my Summary Sheet. Perhaps a radial button can unhide a pivot table when the user has selected a value for its page filter.... What do you think? -- Kent Lysell Financial Consultant Ottawa, Ontario W: 613.948-9557 "Tom Ogilvy" wrote: Seems like I have seen this question posted before. If you didn't get a response, it generally means that the question isn't understood (or less likely, it is not doable or to complex to attempt an answer (someone wants a free project done for them)). I would guess your question is not understood. For me: So the question you ask is: I just need the VBA code that will do the parsing of these values What do you mean by "these values"? What do you mean by parsing? -- Regards, Tom Ogilvy "klysell" wrote: Hi, I have two adjacent columns, occuping Column C and D (range C15:D100), in my summary sheet. The user selects values from a drop-down list in both columns, with one OR the other value in a pair being selected (and the other remaining blank). I would like to dynamically generate pivot tables based on data from my "PivotData" sheet, according to a pre-arranged PT format, with either the value in Column C OR Column D forming the Pivot table page filter. Essentially I'd like a macro to create new worksheets, each with a new pivot table using a page filter from one or the other value of user-chosen, drop-down values in Column C or D. Perhaps once the user has selected values in Column C and D (e.g. for row 15, either C15 or D15 is filled with data using a data validation function), a macro will parse these values in separate worksheets and a pivot table is generated using this values in Columns C and D as page filters. I already have a pivot table designed, and I know that I could turn on the macro recorder and use this code when dynamically generating all the pivot tables. I could then refine this recorded macro to be more efficient (i.e. using knowledge gained from John Walkenbach's Power Programming with VBA book). I just need the VBA code that will do the parsing of these values. What would be my best route to solve this problem? Thanks in advance, -- Kent Lysell Financial Consultant Ottawa, Ontario W: 613.948-9557 |
(repost) Dynamically creating pivot tables from page-field lis
|
| All times are GMT +1. The time now is 12:41 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com