(repost) Dynamically creating pivot tables from page-field list

[Guest]

Hi,

I have two adjacent columns, occuping Column C and D (range C15100), 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,

 

[Guest]

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?

 

[Guest]

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?

 

[Guest]

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.

 

[Guest]

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.

 

[Guest]

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,

 

[Guest]

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

 

[Guest]

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?

 

[Guest]

Here is a fix for that typo and one or two others. This compiles, but I
don't have a pivot table set up to test it with. If you need me to do that,
let me know.

(e-mail address removed)

Private Sub Worksheet_Change(ByVal Target As Range)
Dim pi As PivotItem
Dim sh As Worksheet
If Target.Count > 1 Then Exit Sub
If Target.Column = 3 Or Target.Column = 4 Then

Set sh = Worksheets("PIV_Template").Copy(After:=Worksheets(Worksheets.Count))

If 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

 

[Guest]

Hi Tom,

I'm still getting an "object required" error and the code that is in yellow
is this:
Set sh = Worksheets("PIV_Template").Copy(After:=Worksheets(Worksheets.Count))
If it is ok with you, do you mind if I send you my spreadsheet? Many thanks!

Here is the entirety of the code with my modifications. I've named the
worksheet that has my master pivot table, "PV_template", and the pivot table
is named, "Project_View":

Private Sub Worksheet_Change(ByVal Target As Range)
Dim pi As PivotItem
Dim sh As Worksheet
If Target.Count > 1 Then Exit Sub
If Target.Column = 3 Or Target.Column = 4 Then

Set sh = Worksheets("PIV_Template").Copy(After:=Worksheets(Worksheets.Count))

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