Pivot Table - change page field help

[Roger Govier]

Hi

This thread has got pretty long now.
Perhaps it would be easier if you mailed me a copy of your workbook, and I
will set it up for you.

To mail direct, send to
roger at technology4u dot co dot uk
Change the at and dots to make valid email address

 

[Opal]

Thank you Roger, I appreciate the offer, but I cannot as the
data is proprietory.

Can this be done outside of a module with the code
attached to each sheet and even though each sheet is not
the active sheet, a cell in the sheet will change value based
on a change in the active sheet?

 

[Roger Govier]

Hi

you could have something cascading from sheet to sheet like the following

On Sheet1
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column > 1 Then Exit Sub
If Target.Value = "go" Then
Sheets("Sheet2").Range("a1") = "go"
Sheets("Sheet3").Range("A1") = "go"
End If
End Sub

Sheet2
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Range("a1") = "go" Then
MsgBox ("I'm doing something on Sheet2")
End If
Application.EnableEvents = True
End Sub

Sheet3
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = True
If Range("a1") = "go" Then
MsgBox ("I'm doing something on Sheet3")
End If
Application.EnableEvents = True
End Sub

Try setting up a new work book and entering the code above onto the the
sheet module of sheets 1 2 and 3, then type Go in cell A1 of Sheet1 and you
will get the idea.

If you substitute what you want to do on each sheet with the required code
for updating your PT, rather than Msgbox, then you should be bale to sort
out what you want.

Clearly what you write to each sheet or what location you use in each sheet
is up to you.

 

[Opal]

Hi

you could have something cascading from sheet to sheet like the following

On Sheet1
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column > 1 Then Exit Sub
If Target.Value = "go" Then
Sheets("Sheet2").Range("a1") = "go"
Sheets("Sheet3").Range("A1") = "go"
End If
End Sub

Sheet2
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Range("a1") = "go" Then
MsgBox ("I'm doing something on Sheet2")
End If
Application.EnableEvents = True
End Sub

Sheet3
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = True
If Range("a1") = "go" Then
MsgBox ("I'm doing something on Sheet3")
End If
Application.EnableEvents = True
End Sub

Try setting up a new work book and entering the code above onto the the
sheet module of sheets 1 2 and 3, then type Go in cell A1 of Sheet1 and you
will get the idea.

If you substitute what you want to do on each sheet with the required code
for updating your PT, rather than Msgbox, then you should be bale to sort
out what you want.

Clearly what you write to each sheet or what location you use in each sheet
is up to you.

--
Regards
Roger Govier







- Show quoted text -

Hi Roger, I understand what you have put together
and can see what will happen without having to create
a workbook. I'm still new to coding, but can understand the
logic flow of written code. Actually, its not really a
cascading change, because cell A1 in each sheet will
have a different value -- all 6 sheets cells A1 all change
values at the same time, but the values differ and is from these
new values that I want the pivot tables to update. The new value
is a part number which is generated on another sheet based on
the amount of downtime noted on the particular part in the last
5 production runs. The 6 worst part numbers are generated from
a pivot on another chart. These part numbers are then noted in
a range and linked to each of the 6 pivot table sheets. I have been
using, in a module, the following:

PartNo = Sheets("FSChart1").Range("A1").Value
Sheets("FSChart1").PivotTables("PT1").PivotFields
("PARTNO").CurrentPage = PartNo

but its slow, and I thought if the sub was on the sheet itself, it
would speed up the process, hence why I started this thread in
the hopes of modifying Debra's code to suit my situation.

If it cannot be done, I will continue to muddle through as I have
been.