Synchronize 2 Pivot Tables with a Combo Box

S

sanchdaniel

New Member
Joined
Nov 12, 2010
Messages
3
So I am using this (http://datapigtechnologies.com/flashfiles/pivot10.html) tutorial to synchronize a few pivot tables in one of my workbooks. But it doesn't work correctly because not all my tables have the same values. For example: The matching column name is RepName, and I have multiple tables with RepName as a column, but not all reps are in all tables. I'd like it to select (all) if one of the pivottables does not have a rep. Any help? Excel 2003, but if you guys have a better 2007 code, then I can use 2007.

I've been waiting for an answer from "http://www.excelforum.com," but so far no one has been able to help me out. Here is the link to the other site.

Code: 
Sub ChangePivots()
'
' ChangePivots Macro
' Macro recorded 3/21/2012 by config
'
'
    ActiveSheet.PivotTables("PT1").PivotFields("SalesRepName").CurrentPage = Range("M1").Text
    ActiveSheet.PivotTables("PT4").PivotFields("SalesRepName").CurrentPage = Range("M1").Text




    On Error GoTo Err_SomeName2
     ActiveSheet.PivotTables("PT2").PivotFields("SalesRepName").CurrentPage = Range("M1").Text
    On Error GoTo Err_SomeName2
    
Pivot3:
     
    On Error GoTo Err_SomeName3
    ActiveSheet.PivotTables("PT3").PivotFields("SalesRepName").CurrentPage = Range("M1").Text
    On Error GoTo Err_SomeName3
   
GoTo TheEnd



Err_SomeName2:
    ActiveSheet.PivotTables("PT2").PivotFields("SalesRepName").CurrentPage = Range("(All)").Text
    
Resume Next
    
Err_SomeName3:
    ActiveSheet.PivotTables("PT3").PivotFields("SalesRepName").CurrentPage = Range("(All)").Text
    


Resume Next

TheEnd:
End Sub
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Sort by date Sort by votes
Hi,

maybe this code could get you started...(not sure if i wrote this code the most efficient way but it works =))..

Sub ChangePivots()
Dim pt As PivotTable
Dim pti As PivotItem
Dim xx As String

xx = ActiveSheet.Range("M1").Value 'value of your pagefilter
For Each pt In ActiveSheet.PivotTables
For Each pitm In pt.PageFields("SalesRep").PivotItems
If pitm.Value = xx Then
GoTo n:
End If
Next
pt.PageFields("SalesRep").CurrentPage = "(All)"
GoTo x:

n:
pt.PageFields("SalesRep").CurrentPage = xx
x:
Next pt
End Sub

hope this helps
-Jax
 
Upvote 0
:ROFLMAO:

I got an answer, thank you so much for responding.

So the code seems to work, but I get an error saying

Run-time error '1004':
Application-defined or object-defined error.

When I check the pivot tables, only the first pivot table changed, the rest don't seem to be affected.
 
Upvote 0
is your pivot tables located in 1 sheet or multiple sheets?
when encounter the error and click debug, which line does it returns error?
 
Upvote 0
Yup, they are all in the same sheet.

One of the pivot tables doesn't have a pagefield of "SalesRep", or any pagefield for that matter. So I took it out and it now works :biggrin:, but I'd like to keep it in the same sheet, if possible.

In the mean time, I'll just make another sheet with that specific table else where. Also, you are now the awesome-est person in my book.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
Pivot Table VBA Help
Replies
0
Views
452
ashley1984
A
S
  • Question
Simplified VBA code to generate faster output
Replies
1
Views
249
shansakhi
S
S
  • Question
Excel VBA Getting error when creating pivot chart but error only occurs after sub that creates pivot tables. No error if directly called
Replies
0
Views
674
Steve48
S
S
  • Solved
VBA to create pivot tables
2
Replies
12
Views
2K
Domenic
D
B
  • Question
VBA - optimising multiple pivot tables code
Replies
0
Views
705
bukimi
B

Forum statistics

Threads
1,216,075
Messages
6,128,668
Members
449,463
Latest member
Jojomen56

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top