pivot table created from another pivot table

[Guest]

I was able to create a pivot table using an established pivot table; however,
the established pivot table has filters (created using the PAGE area of the
layout). I was hoping that when the established pivot table is filtered on
(thus changing the data within the pivot), it would update the newly created
pivot with the same filters. Is there something I should do that would
acheive what I desire in this second pivot table? Thanks!

 

[Debra Dalgleish]

You can do this with programming. There's a sample file here:

http://www.contextures.com/excelfiles.html

Under PivotTables, look for 'Change Page Field'

 

[Guest]

This is very much on the track that I need to be on. From what I see in the
sample file, it is in visual basic that I would need to do this programming.
I can somewhat follow what programming; however, I have a few attributes in
the Page field that I would need to link. What could I do with this
programming to help me get there?

Here is the programming that I copied:

Option Explicit

Dim mvPivotPageValue As Variant

Private Sub Worksheet_Calculate()
Dim wsOther As Worksheet
Dim pt As PivotTable
Dim pt1 As PivotTable
Dim pt2 As PivotTable
Dim strField As String

Set wsOther = Sheets("Other Pivots")
Set pt = Me.PivotTables(1)
Set pt1 = wsOther.PivotTables(1)
Set pt2 = wsOther.PivotTables(2)
strField = "Market"

If LCase(pt.PivotFields("Market").CurrentPage) <> LCase(mvPivotPageValue) Then
Application.EnableEvents = False
pt.RefreshTable
mvPivotPageValue = pt.PivotFields(strField).CurrentPage
pt1.PageFields(strField).CurrentPage = mvPivotPageValue
pt2.PageFields(strField).CurrentPage = mvPivotPageValue
Application.EnableEvents = True
End If

End Sub

Any ideas? Thanks!

 

[Debra Dalgleish]

If you provide more information on what you're trying to do, someone
might be able to help you adapt the code to your worksheet.

 

[Guest]

Hi again,

I have a pivot table with four items in the "page area" on which users can
filter to customize the report much like this:

Market (All)
City (All)
Service (All)
Price (All)

Data
Group Type Client Hours Revenue
Group 1 Type 1 Client 1 5 100
Client 1 80 400
Client 1 100 500
Client 1 200 1,000


I would like to have it link to several other reports within the same
workbook. Through my original question, I was directed to
http://www.contextures.com/excelfiles.html, which help me somewhat link pivot
table based on a string field. It worked wonderfully when I was using one
attribute in the "page area" (for example "Market") but I would like to carry
that functionality through the other three attributes of the "page area" (i.e
city, service, and price). I included the programming found in the sample
provided on http://www.contextures.com/excelfiles.html, if someone can help
me go a step further by directing me to do the same functionality for several
attributes found in the "page area", I would be most appreciative!!

Also, do all the pivot table have to come from the same data source?

Many thanks!!

 

[Debra Dalgleish]

You could add more variables to the code, to accommodate the number of
page fields. I've added a sample file to my web site, that shows the
code for two page fields:

http://www.contextures.com/excelfiles.html

Under PivotTables, look for 'Change Multiple Page Fields'

 

[Guest]

This is beautiful, thank you so much!!

You could add more variables to the code, to accommodate the number of
page fields. I've added a sample file to my web site, that shows the
code for two page fields:

http://www.contextures.com/excelfiles.html

Under PivotTables, look for 'Change Multiple Page Fields'