Synchronize Two Pivot Tables with Combo Box

enzo · Mar 23, 2007

Hi friends,

As the title suggests, I'm trying to sync a common field in two different pivot tables.

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 in the Combo box, the same project name would seamlessly be selected in both tables.

Each table would populate with its own data based on the project selected.

Basically, I'd like to use the method illustrated in this Flash file:
http://www.mrexcel.com/wiki/index.php?n=ExcelWiki.SynchronizeTwoPivotTablesWithAComboBox

This method would have worked beautifully if it weren't for this reason:

Table 1: Project data is in the PAGE field
(single selection)

Table 2: Project data is in the ROW field (multi-selection)

If the Projects data was in the PAGE field in both tables, my code would look like this:

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

ActiveSheet.PivotTables("Table 2").PivotFields("Project").CurrentPage = _ActiveSheet.Range("X1").Value

But no, because the ROW field is a multi-select one, I get this kind of code:

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

With ActiveSheet.PivotTables("Table 2").PivotFields("Project")
.PivotItems("Project 1").Visible = False
.PivotItems("Project 2").Visible = False
.PivotItems("Project 3").Visible = False
.PivotItems("Project 4").Visible = False
.PivotItems("Project 5").Visible = False
.PivotItems("Project 6").Visible = False

End With
End Sub

There are 98 project names. How can I work this out? Surely there is a way.

Reid · Mar 25, 2007

You need to loop through the PivotItems in PivotField("Project"). First, though you should make the PivotItem you want visible. If, during tthe course of your loop, you accidentally try to make all PivotItems invisible, you will get an error.

Something like this

Code:

Dim pItem As PivotItem

ActiveSheet.PivotTables("Table 2").PivotFields("Project").PivotItem("Project 1").Visible = True
For Each pItem In ActiveSheet.PivotTables("Table 2").PivotFields("Project")
    If pItem.Value <> "Project 1" Then pItem.Visible = False
Next pItem

enzo · Mar 26, 2007

I'm tremendously grateful for your help Reid. But this report reminds me why I dropped that computer science major.

I'm getting this error:
Run-time error '1004'
Unable to set the Visible property of the PivotItem class

Code:

Dim pItem As PivotItems


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

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


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

etc...on like this to the end

Reid · Mar 26, 2007

Try this:

Code:

If Not .PivotItems("Project 2").Visible Then .PivotItems("Project 2").Visible = True

instead of:

Code:

.PivotItems("Project 2").Visible = True

Then make the same change for Project 3. Not only do you get a runtime error if you try to make all pivotitems invisible, you get one if you try to set the visible property to True when it already is True. Which I am thinking is what is happening here. You could also bracket the problematic statements with On Error Resume Next/On Error Goto 0, but this is a little safer in case there is something else going on.

Hope this helps.

enzo · Mar 26, 2007

Now I'm getting run-time error 438 on the these 2 lines
Object doesn't support this object or method

Code:

For Each pItem In ActiveSheet.PivotTables("Table 2").PivotFields("Project")
    If pItem.Text <> "Project 2" Then pItem.Visible = False

I dunno...I'm close to throwing in the towel on this one.

Reid · Mar 26, 2007

Don't give up yet! It should be pItem.Value not pItem.Text.

enzo · Mar 26, 2007

Yeah, I tried pItem.Value first...still doesn't work.

Why me? Whyyyyyyyyyyyyyyyyy?????

Reid · Mar 26, 2007

I'm not sure why you are getting an error. If I use this code on this PivotTable, it works fine. One other question, are you sure that you have a "Project 2" PivotItem?

Code:

Sub PivotItemSelect()

    Dim pTable As PivotTable, pField As PivotField, pItem As PivotItem
    
    Set pTable = ActiveSheet.PivotTables("PivotTable2")
    Set pField = pTable.PivotFields("Project")
    
    If Not pField.PivotItems("Project 2").Visible Then pField.PivotItems("Project 2").Visible = True
    For Each pItem In pField.PivotItems
        If pItem.Value<> "Project 2" Then pItem.Visible = False
    Next pItem

End Sub

Book2

A

B

C

D

1

SumofAmount

Date

2

Project

1/1/2007

1/2/2007

GrandTotal

3

Project1

10

20

4

Project2

10

20

5

Project3

10

20

6

Project4

10

20

7

GrandTotal

40

80

Sheet1

enzo · Mar 27, 2007

Hey Reid,
Yeah, I was wondering about my project names too and double-checked them; all ok. Following your phenomenal guidance and help from other programmers on another board, I've managed to get it close to working!

There is still a problem though.

Here's the summary; just as a refresher:

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.

Code:

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 .PivotItems("Project B").Visible = True
For Each pItem In ActiveSheet.PivotTables("Table 2").PivotFields("Project").PivotItems
If pItem.Value <> "Project B" Then pItem.Visible = False
Next pItem
End With

Code:

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

Reid · Mar 28, 2007

Well, there is no need to hard code the combo box value like that. Give this a try:

Code:

Sub ProjectLinking()

    Dim pItem As PivotItem
    Dim strSelection As String
    
    strSelection = Range("X1").Value
    
    ActiveSheet.PivotTables("Table 1").PivotFields("Project").CurrentPage = _
            strSelection
              
    With ActiveSheet.PivotTables("Table 2").PivotFields("Project")
    If Not .PivotItems(strSelection).Visible = True Then .PivotItems(strSelection).Visible = True
    For Each pItem In ActiveSheet.PivotTables("Table 2").PivotFields("Project").PivotItems
        If pItem.Value <> strSelection Then pItem.Visible = False
    Next pItem
    End With

End Sub

Hope it helps.

(Edit: Left over hard coded reference to "Project A" replaced with strSelection.)

Synchronize Two Pivot Tables with Combo Box

enzo

New Member

Reid

Well-known Member

enzo

New Member

Reid

Well-known Member

enzo

New Member

Reid

Well-known Member

enzo

New Member

Reid

Well-known Member

enzo

New Member

Reid

Well-known Member

Similar threads

Share this page

Synchronize Two Pivot Tables with Combo Box

New Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

Similar threads

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock