Pivot table help needed

  • Thread starter Thread starter Paul Falla
  • Start date Start date
P

Paul Falla

I have a number of worksheets in a workbook. Each sheet
contains pivot tables with year and month headers. What I
am trying to do is have some sort of control (eg macro
attached to a button) which will ask the user which year
and month they wish to view, and then update each of the
pivot tables accordingly. Currently the user has to do
this manually on each sheet, and as all they do is use the
sheets for reporting puposes, this becomes rather
repetitive over 20+ worksheets.

Any help gratefully received
 
The following code is adapted from a posting by Robert Rosenberg. It
changes all Pivot Tables if the page is changed on the first PT. You
could revise it to suit your layout. As noted in the code, place the
code on the module for the worksheet which contains the first Pivot
Table. Or, add the code to each sheet with a pivot table, so a change to
one will change all (use a different module level variable name on each
sheet).

Dim mvPivotPageValue As Variant
Private Sub Worksheet_Calculate()
'by Robert Rosenberg 2000/01/11
''I use a module level variable (see above) to keep track of
''the last selection from the Page Field.
''This routine was place in the Worksheet
''containing the PivotTable's code module.
Dim pvt As PivotTable
Dim pvt2 As PivotTable
Dim ws As Worksheet

Set pvt = ActiveSheet.PivotTables(1)
If LCase(pvt.PivotFields("Year").CurrentPage) _
<> LCase(mvPivotPageValue) Then
For Each ws In ActiveWorkbook.Worksheets
For Each pvt2 In ws.PivotTables
'The Page Field was changed
Application.EnableEvents = False
pvt.RefreshTable
mvPivotPageValue = _
pvt.PivotFields("Year").CurrentPage
pvt2.PageFields("Year").CurrentPage _
= mvPivotPageValue
Application.EnableEvents = True
Next pvt2
Next ws
End If
End Sub
 
Dear Debra
You are a star - that bit of code has worked a treat -
Thanks V. Much

Paul
 
Back
Top