Newbie problem with Worksheet_Change

  • Thread starter Thread starter pkern
  • Start date Start date
P

pkern

I'm working with Excel 2007 and trying to have the report filter in one pivot
table automatically chage the 2nd pivot table on the same sheet. I'm new and
having some problems. any help would be appreciated. Here is my code.

My main pivot table is called PVT1
Fiscal_Cal is the field my report filter is in
Fiscal_Calendar_Check is the field beside for checking
PVT2 is the second table
PVT2Date is the date the report filter is in

Here is my code

Private Sub Worksheet_Change(ByVal Target As Range)

If Range("Fiscal_Cal") <> Range("Fiscal_Calendar_Check") Then
Application.ScreenUpdating = False
Else

Exit Sub

End If


If Range("Fiscal_Cal") <> Range("Fiscal_Calendar_Check") Then
Range("Fiscal_Calendar_Check") = Range("Fiscal_Cal")
ActiveSheet.PivotTables("PVT2").PivotFields("PVT2Date").CurrentPage
= Range("Fiscal_Cal").Value
End If

Application.ScreenUpdating = True

End Sub

What am i missing?
 
Download the files and take a look. You are not all wrong but you certainly
are not right. The example does exactly what you have asked...
 
Hi Jim,

I down loaded the code for PivotMulti and is the Change.xls and changed mine
accordingly. Two things. The PivotMultiChange did not work and is the
strField("Item") the name of the Report Filter. I'm a bit confused there.
Here is my code. Thank you for being so helpful and patient.

Private Sub Worksheet_Calculate()
Dim wsOther As Worksheet
Dim pt As PivotTable
Dim pt1 As PivotTable
Dim strField As String

Set wsOther = Sheets("Division Summary") 'only 1 sheet
Set pt = PVT1 'main table
Set pt1 = wsOther.PVT2
strField = "Fiscal Calendar"

If LCase(pt.PivotFields("Fiscal Calendar").CurrentPage) <>
LCase(mvPivotPageValue) Then
'The Page Field was changed
Application.EnableEvents = False
pt.RefreshTable
mvPivotPageValue = pt.PivotFields(strField).CurrentPage
pt1.PageFields(strField).CurrentPage = mvPivotPageValue
Application.EnableEvents = True
End If
End Sub
 
Back
Top