Newbie problem with Worksheet_Change
 

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("PVT2D ate").CurrentPage
= Range("Fiscal_Cal").Value
End If

Application.ScreenUpdating = True

End Sub

What am i missing?

Newbie problem with Worksheet_Change
 

Check out this link... there are some workbooks you cna download that
demonstrate exactly what you are trying to do...

http://www.contextures.com/excelfiles.html#Pivot
--
HTH...

Jim Thomlinson


"pkern" wrote:

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("PVT2D ate").CurrentPage
= Range("Fiscal_Cal").Value
End If

Application.ScreenUpdating = True

End Sub

What am i missing?

Newbie problem with Worksheet_Change
 

does this mean that what I have will not work?


"Jim Thomlinson" wrote:

Check out this link... there are some workbooks you cna download that
demonstrate exactly what you are trying to do...

http://www.contextures.com/excelfiles.html#Pivot
--
HTH...

Jim Thomlinson


"pkern" wrote:

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("PVT2D ate").CurrentPage
= Range("Fiscal_Cal").Value
End If

Application.ScreenUpdating = True

End Sub

What am i missing?

Newbie problem with Worksheet_Change
 

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...
--
HTH...

Jim Thomlinson


"pkern" wrote:

does this mean that what I have will not work?


"Jim Thomlinson" wrote:

Check out this link... there are some workbooks you cna download that
demonstrate exactly what you are trying to do...

http://www.contextures.com/excelfiles.html#Pivot
--
HTH...

Jim Thomlinson


"pkern" wrote:

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("PVT2D ate").CurrentPage
= Range("Fiscal_Cal").Value
End If

Application.ScreenUpdating = True

End Sub

What am i missing?

Newbie problem with Worksheet_Change
 

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
"Jim Thomlinson" wrote:

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...
--
HTH...

Jim Thomlinson


"pkern" wrote:

does this mean that what I have will not work?


"Jim Thomlinson" wrote:

Check out this link... there are some workbooks you cna download that
demonstrate exactly what you are trying to do...

http://www.contextures.com/excelfiles.html#Pivot
--
HTH...

Jim Thomlinson


"pkern" wrote:

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("PVT2D ate").CurrentPage
= Range("Fiscal_Cal").Value
End If

Application.ScreenUpdating = True

End Sub

What am i missing?