By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,496 Members | 1,528 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,496 IT Pros & Developers. It's quick & easy.

Updating Pivot Table Page Fields based on another page field

P: 31
I have been working with VBA for less than a year, but mostly in Access. I have come accross an issue in Excel that I want to solve programatically.

I have several pivot tables on one work sheet that feed several pivot charts.

I am looking for code that will update each pivot table based on the page I select from the first pivot table on that worksheet.

Basically, once I update the first pivot table, I need each of the remaining pivots to get the same update.

If I can get this to work, it will save me a ton of time.

I appreciate any help you give me.

A
Jun 2 '09 #1
Share this Question
Share on Google+
3 Replies


NeoPa
Expert Mod 15k+
P: 31,561
I've not worked with these objects before but I've done some VBA. If you'd like to post your working spreadsheet I'll see what I can do for you.
Jun 4 '09 #2

P: 31
Thanks NeoPa. But I found some code on line. I forgot the link, but here is the code that worked for me.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2.     Dim pt As PivotTable
  3.  
  4.     If Not Intersect(Target(1, 1), Range("Branchcode")) Is Nothing Then
  5.         Set pt = Sheet9.PivotTables(1)
  6.         pt.RefreshTable
  7.     With pt
  8.         .RefreshTable
  9.         .PivotFields("Office Code").CurrentPage = Range("Branchcode").Text
  10.  
  11.         End With
  12.  
  13.     End If
  14.  
  15. End Sub
I simply changed the pivot table # to have it update 9 different pivot tables and subsequent charts.

Thank you for stepping up to help though.
Jun 5 '09 #3

NeoPa
Expert Mod 15k+
P: 31,561
Ah. Well done.

With a little tweaking (& tidying) I think we can handle the 9 pivot tables (assuming they're in the one worksheet - Sheet9 from your code). Try this :
Expand|Select|Wrap|Line Numbers
  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2.     Dim intX As Integer
  3.  
  4.     If Not Intersect(Target(1, 1), Range("Branchcode")) Is Nothing Then
  5.         For intX = 1 To 9
  6.             With Sheet9.PivotTables(intX)
  7.                 Call .RefreshTable
  8.                 .PivotFields("Office Code").CurrentPage = Range("Branchcode").Text
  9.             End With
  10.         Next intX
  11.     End If
  12. End Sub
Jun 5 '09 #4

Post your reply

Sign in to post your reply or Sign up for a free account.