On Mar 10, 2:27*pm, Opal wrote:
On Mar 10, 11:47*am, "Roger Govier"





<roger@technology4unospamdotcodotuk wrote:
Hi

Just remove the 2 lines

*For Each ws In ThisWorkbook.Worksheets
*and
Next ws

--
Regards
Roger Govier

"Opal" wrote in message

....

I have a workbook in Excel 2003 with multiple worksheets.
I want to be able to change the page fields in only one
worksheet, not all the worksheets. *I have found
Debra Dalgleish's website very helpful, but her code is
to change the page field in all pivots in the workbook, I only
want to change the page fields on 2 pivot tables on one sheet.
Could someone please give me some direction as to how
I can modify this code to change only the 2 pivots on
one sheet?

Private Sub Worksheet_Change(ByVal Target As Range)

Dim ws As Worksheet
Dim pt As PivotTable
Dim pi As PivotItem
Dim strField As String

strField = "Region"

On Error Resume Next
Application.EnableEvents = False
Application.ScreenUpdating = False

* *If Target.Address = Range("D2").Address Then

* * * *For Each ws In ThisWorkbook.Worksheets
* * * * * *For Each pt In ws.PivotTables
* * * * * * * *With pt.PageFields(strField)
* * * * * * * * * *For Each pi In .PivotItems
* * * * * * * * * * * *If pi.Value = Target..Value Then
* * * * * * * * * * * * * *.CurrentPage = Target.Value
* * * * * * * * * * * * * *Exit For
* * * * * * * * * * * *Else
* * * * * * * * * * * * * *.CurrentPage = "(All)"
* * * * * * * * * * * *End If
* * * * * * * * * *Next pi
* * * * * * * *End With
* * * * * *Next pt
* * * *Next ws

* *End If

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

Thank you....my sheet is FSChart1, pivot tables are PT1 and PT2.- Hide quoted text -

- Show quoted text -

Roger, I have 6 sheets I need to do this to... I have put the routine
on each
sheet, but removing the 2 lines does not do it....- Hide quoted text -

- Show quoted text -

my coding skills are weak...I have something like this:

Dim ws As Worksheet
Dim pt1 As PivotTable
Dim pt2 As PivotTable
Dim pi As PivotItem
Dim strField As String

strField = "PARTNO"

On Error Resume Next
Application.EnableEvents = False
Application.ScreenUpdating = False

Set pt1 = Worksheets("FSChart1").PivotTables(1)
Set pt2 = Worksheets("FSChart1").PivotTables(2)


If Target.Address = Range("A1").Address Then

' For Each pt In ws.PivotTables
' With pt1.PageFields(strField)
' For Each pi In .PivotItems
If pi.Value = Target.Value Then
.CurrentPage = Target.Value
Exit For
Else
.CurrentPage = "(All)"
End If
' Next pi
' End With
' Next pt2

But I am getting no where fast.... Can you point me in the right
direction?