Hi,

I have the following problem:

I would like to use Excel XL Pivot based on MS AS 2000 cuves for
example Foodmart 2000.

In 1 Excel file I have 2 sheets and on each sheet a different pivo
table based on a different cube.

--

I would like to have the cubes synchronized. So If I choice for example
the citie for the Foodmart sales cube that the other pivot is also
changed.

Is this possible ?

---

Is it possible to have a send a parameter to the pivot in Excel and
have as result back both the 2 new pivots ?

---
In Panorama NovaView you have for example dependent views. But I would
like to use the default features of Excel and not the Excel addin.

Regards, Marco

I answered a similar issue, earlier today, but this person had two
pivottables on the one sheet. I have modified the sample to look across
all the sheets in the current workbook and to synchronise all the
pivottables it finds.



'\\ This global flag stops event recursion
Dim bUpdating As Boolean

'\\ ================================================== ===========
'\\ Author : Darren Gosbell
'\\ Date : 12 Dec 2005
'\\ Description: Keeps the page fields of multiple PivotTables
'\\ on the same sheet synchronised.
'\\ ================================================== ===========
Sub UpdateAllPivotTables(target As PivotTable)
Dim pt As PivotTable
Dim pf As PivotField
Dim sht As Worksheet

If Not bUpdating Then
bUpdating = True
Me.Application.ScreenUpdating = False

'\\ For each pivot table on the current sheet
'\\ find any pagefields with the same name as those
'\\ in the sheet that was just changed and synchronise
'\\ them.
For Each sht In ThisWorkbook.Worksheets
For Each pt In sht.PivotTables
If pt.Name <> target.Name Then
For Each pf In target.PageFields
If Not pt.PageFields(pf.Name) Is Nothing Then
pt.PageFields(pf.Name).CurrentPageName =
pf.CurrentPageName
End If
Next pf

End If
Next pt
Next sht
Columns("B:B").EntireColumn.AutoFit
Me.Application.ScreenUpdating = True

bUpdating = False
End If
End Sub


'************************************************* ****************
'** This needs to be added to the code in every sheet that you
'** want to trigger a change from.
'************************************************* ****************
Private Sub Worksheet_PivotTableUpdate(ByVal target As PivotTable)
UpdateAllPivotTables target
End Sub



--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <1134337268.457537.214240 (AT) f14g2000cwb (DOT) googlegroups.com>,
olap (AT) gmsbv (DOT) nl says...

Hi,

I have some problems in the vb code. Can you mail (olap at gmsbv.nl) me
a sample excel sheet based on foodmart 2000 with the vb code in the
needed sheets. For example sheet 1 and 2 with a pivot table

Thank you very much.

Marco


Darren Gosbell schreef:

Outstanding!

Been looking for something like this for ages.

Thanks Darren.

"Darren Gosbell" wrote:

Hi Darren,

Maybe you can update the script with the loop. It is here also giving
some errors when there are slicers not in the page field filter area.

I am testing also the other script now.

Regards, Marco

MarkFrank schreef:

Marco,

Below is the updated script, which is the same as the one in the Excel
file I sent you off-line. Is this still causing issues? I had a fairly
simple set of pivot tables that a client setup that I was keeping in
synch with a similar script, but it has not been widely tested against
other scenarios.

I am not sure what you mean by "slicers not in the page field filter
area", can you elaborate further?

As I told you off-line, the script below fixes a bug where the number of
page fields were not the same between the different tables. I basically
re-coded the "For Each" loop into a "For...Next" and used an index into
the pagefields collection. (the for each iterator was getting completely
confused somehow.)



'\\ This global flag stops event recursion
Dim bUpdating As Boolean

'\\ ================================================== ===========
'\\ Author : Darren Gosbell
'\\ Date : 12 Dec 2005
'\\ Description: Keeps the page fields of multiple PivotTables
'\\ on the same sheet synchronised.
'\\ ================================================== ===========
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim pt As PivotTable
Dim pf As PivotField
Dim I As Integer

If Not bUpdating Then
bUpdating = True
Me.Application.ScreenUpdating = False

'\\ For each pivot table on the current sheet
'\\ find any pagefields with the same name as those
'\\ in the sheet that was just changed and synchronise
'\\ them.
On Error Resume Next

For Each pt In Me.PivotTables
If pt.Name <> Target.Name Then
For I = 1 To Target.PageFields.Count
Set pf = Nothing

Set pf = pt.PageFields(Target.PageFields(I).Name)
If Not pf Is Nothing Then
pt.PageFields(Target.PageFields
(I).Name).CurrentPageName = Target.PageFields(I).CurrentPageName
End If
Next I

End If
Next pt

Columns("B:B").EntireColumn.AutoFit
Me.Application.ScreenUpdating = True

bUpdating = False
End If
End Sub



--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <1134478085.868760.30890 (AT) g44g2000cwa (DOT) googlegroups.com>,
olap (AT) gmsbv (DOT) nl says...

I have created a workbook that will update all pivot tables on any sheet
in the workbook, you can find a link to download it on my blog at:

http://geekswithblogs.net/darrengosb.../14/63085.aspx

I think this code is generic enough to handle most situations, but let
me know if you run into any issues.

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <1134478085.868760.30890 (AT) g44g2000cwa (DOT) googlegroups.com>,
olap (AT) gmsbv (DOT) nl says...

Darren,

Thank you very much for this solution. I have a feeling that a lot of
people are waiting for this solution for years !

Regards,
Marco Groeneveld

www.gmsbv.nl

Darren Gosbell schreef: