Join 2 pivot tables with 2 different data sources 2

[jnevi]

I need help my friends. Can't figure this out.

I have 2 pivot tables on a single worksheet.
Each table has its own data source - 2 different databases (had to set it up that way to present all the data requested).

The 2 data sources have one column of data in common. This column is called Projects and all project names are the same in both databases.

BUT...

Pivot Table 1: Projects is in the ROW area (multi-select dropdown)

Pivot Table 2: Projects is in the PAGE area (single select drop-down)

I would like to link the Projects data items so that when I select a project name on Table 1, the same project name would automatically and seamlessly be selected on Table 2.


Data should not be pulled from one source to another.
NO

I just need simultaneous selections and data from each source to be displayed in its proper table.

Is this possible?

 

[SkipVought]

Hi,

Turn on your macro recorder and record selecting the project in each pivot table.

Observe your recorded code and modify as needed.

Post back with code if you have specific questions.

Skip,

_[red][/red]

 

[jnevi]

Hi Skip,

I've also been requesting help with this issue on another board:

http://www.mrexcel.com/board2/viewtopic.php?p=1273416#1273416

I just can't get this to work.
I decided on the method illustrated in this clip.

http://www.mrexcel.com/wiki/index.php?n=ExcelWiki.SynchronizeTwoPivotTablesWithAComboBox

Basically I created a combo box. I should be able to select a project name from that box's dropdown and the same project would be selected in both tables. Brilliant idea if it weren't for the fact that my 'Project' data are not in the page field on both tables.



Here is my current broken code:

Dim pItem As PivotItems


ActiveSheet.PivotTables("Table 1").PivotFields("Project").CurrentPage = _
        ActiveSheet.Range("X1").Value
       

With ActiveSheet.PivotTables("Table 2").PivotFields("Project")
If Not .PivotItems("Project 1").Visible Then .PivotItems("Project 1").Visible = True
[highlight]For Each pItem In ActiveSheet.PivotTables("Table 2").PivotFields("Project")[/highlight]
If pItem.Text <> "Project 1" Then pItem.Visible = False
Next pItem
End With


With ActiveSheet.PivotTables("Table 2").PivotFields("Project")
If Not .PivotItems("Project 2").Visible Then .PivotItems("Project 2").Visible = True
For Each pItem In ActiveSheet.PivotTables("Table 2").PivotFields("Project")
    If pItem.Text <> "Project 2" Then pItem.Visible = False
Next pItem
End With

With ActiveSheet.PivotTables("Table 2").PivotFields("Project")
If Not .PivotItems("Project 3").Visible Then .PivotItems("Project 3") = True
For Each pItem In ActiveSheet.PivotTables("Table 2").PivotFields("Project")
    If pItem.Text <> "Project 3" Then pItem.Visible = False
Next pItem
End With

The For Each statement generates Run-time Error 438: Object doesn't support this object or method.
Any ideas? Am I proceeding correctly?

Thanks for ur time,
J

 

[SkipVought]

For Each pItem In ActiveSheet.PivotTables("Table 2").PivotFields("Project")[b].PivotItems[/b]
'...

Next

Skip,

_[red][/red]

 

[jnevi]

I get this error Skip:
Run-time error '13'
Type Mismatch

For Each pItem In ActiveSheet.PivotTables("Details").PivotFields("Project").PivotItems

 

[PHV]

Dim pItem As PivotItem

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jnevi]

It doesn't like my IF statement.

Error 438: Object doesn't support this property or method

for the highlighted part.

With ActiveSheet.PivotTables("Table 2").PivotFields("Project")
[highlight]If Not .PivotItem("Project 1").Visible = True Then[/highlight] PivotItems.Visible = True
For Each pItem In ActiveSheet.PivotTables("Table 2").PivotFields("Project").PivotItems
If pItem.Value <> "Project 1" Then pItem.Visible = False
Next pItem
End With

 

[PHV]

If Not .PivotItem[!]s[/!]("Project 1").Visible = True Then PivotItems[!]("Project 1")[/!].Visible = True

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jnevi]

Yep, that was it PHV!!! But now there is something else...
I think it would be good to sum things up again too.

How It Should Work:
There is a combo box at the top of the worksheet - It contains a dropdown list of all the project names.
The numerical value in cell X1 corresponds to a project name in the combo box.
This macro I'm writing is assigned to the combo box.

I select a project name in the combo box at the top.
That project name becomes active in *both* tables and the corresponding data for each selection populates each respective table.


Tricky part:
There are 2 pivot tables on this worksheet, side-by-side.
In Table 1, the Projects field is in the Page Area of the pivot table. (single-select)
In Table 2, the Projects field is in the Row Area of the pivot table. (multi-select checkboxes)


Okay.

The code below works!...until...I select a project name other than Project A.

The first part of the code says make Project A visible and all others not visible.
Well, no matter what selection I make in the combo box only Project A will ever be selected in Table 2.
Table 1 is being good and cooperating.

Selecting any project name other than Project A generates this error message:

Run-time error '1004'
Unable to set the Visible property of the PivotItem class.

The debugger highlights the part I've highlighted here. It can't make Project B true because I previously told it to make
Project A true and all others false.


I'm missing something.

Sub ProjectLinking()


Dim pItem As PivotItem


ActiveSheet.PivotTables("Table 1").PivotFields("Project").CurrentPage = _
        ActiveSheet.Range("X1").Value
       

With ActiveSheet.PivotTables("Table 2").PivotFields("Project")
If Not .PivotItems("Project A").Visible = True Then .PivotItems("Project A").Visible = True
For Each pItem In ActiveSheet.PivotTables("Table 2").PivotFields("Project").PivotItems
    If pItem.Value <> "Project A" Then pItem.Visible = False
Next pItem
End With



With ActiveSheet.PivotTables("Table 2").PivotFields("Project")
If Not .PivotItems("Project B").Visible = True Then [highlight].PivotItems("Project B").Visible = True[/highlight]
For Each pItem In ActiveSheet.PivotTables("Table 2").PivotFields("Project").PivotItems
    [highlight red]If pItem.Value <> "Project B" Then pItem.Visible = False[/highlight]
Next pItem
End With


With ActiveSheet.PivotTables("Table 2").PivotFields("Project")
If Not .PivotItems("Project C").Visible = True Then .PivotItems("Project C").Visible = True
For Each pItem In ActiveSheet.PivotTables("Table 2").PivotFields("Project").PivotItems
    If pItem.Value <> "Project C" Then pItem.Visible = False
Next pItem
End With


End Sub

 

[SkipVought]

The problem is that they cannot ALL be FALSE.

Set them all TRUE.

Then selectively set the ones FALSE.

Skip,

_[red][/red]