Pivot Table - change page field help

[Opal]

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.

 

[paul.robinson]

Hi
Each pivottable on a sheet is numbered. If you have 2 on a sheet then
try
Dim pt1 as PivotTable, pt2 as PivotTable
Set pt1 = Worksheets("MySheet").PivotTables(1)
Set pt2 = Worksheets("MySheet").PivotTables(2)

In your code remove the looping and change pt1 and pt2 individually.
This is untested so you may need to fiddle with it a bit.

regards
Paul

 

[Roger Govier]

Hi

Just remove the 2 lines

For Each ws In ThisWorkbook.Worksheets
and
Next ws

 

[Opal]

Hi

Just remove the 2 lines

 For Each ws In ThisWorkbook.Worksheets
 and
Next ws

--
Regards
Roger Govier
















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

 

[Opal]

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?

 

[Opal]

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?- Hide quoted text -

- Show quoted text -

This also works....

Private Sub PartNoChart1()

Dim PartNo As String

On Error Resume Next
PartNo = Sheets("FSChart1").Range("A1").Value
Sheets("FSChart1").PivotTables("PT1").PivotFields
("PARTNO").CurrentPage = PartNo
Sheets("FSChart1").PivotTables("PT2").PivotFields
("PARTNO").CurrentPage = PartNo

End Sub

 

[Roger Govier]

Hi

I only said the remove 2 lines from the code
For Each ws In ThisWorkbook.Worksheets
and
Next ws

Regrettably I did not look closely enough at the code, you also need to
change the line
For Each pt In ws.PivotTables
to
For Each pt In ActiveSheet.PivotTables

The complete code should be as follows.
This code should be pasted on the sheet where the 2 PT's you want to change
are located.

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 pt In ActiveSheet.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

End If

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

--
Regards
Roger Govier

 

[Opal]

Thank you for the follow-up, Roger, but it still didn't work....
I got this working:

Sub PartNoChart1()


Dim PartNo As String


On Error Resume Next
PartNo = Sheets("FSChart1").Range("A1").Value
Sheets("FSChart1").PivotTables("PT1").PivotFields
("PARTNO").CurrentPage = PartNo
Sheets("FSChart1").PivotTables("PT2").PivotFields
("PARTNO").CurrentPage = PartNo


End Sub

 

[Roger Govier]

Hi

It worked perfectly for me.
If you would like to mail me a copy of your workbook, I would be interested
in seeing why it wouldn't work in your case.
To mail direct, send to
roger at technology4u dot co dot uk
Change the at and dots to make valid email address

 

[Opal]

Hi

It worked perfectly for me.
If you would like to mail me a copy of your workbook, I would be interested
in seeing why it wouldn't work in your case.
To mail direct, send to
roger at technology4u dot co dot uk
Change the at and dots to make valid email address

--
Regards
Roger Govier










- Show quoted text -

I appreciate your offer to go that extra mile for me,
unfortunately, the data is proprietory and I cannot
send it off to you.

However, I do have a question about the line:

For Each pt In ActiveSheet.PivotTables

The sheet with the pivot tables would not be my
active sheet. The tables are in other work
sheets and feed charts found on my main sheet
in the workbook. There are 11 worksheets in
total with various pivots feeding charts on the
main sheet.

 

[paul.robinson]

Hi
Change Activesheet to your sheet name e.g. Worksheets ("MySheet"). If
you only have 2 sheets to look at you will do the remove the loop bit
and do the pagefield change for each pivot table separately.
regards
paul

 

[Opal]

Hi
Change Activesheet to your sheet name e.g. Worksheets ("MySheet"). If
you only have 2 sheets to look at you will do the remove the loop bit
and do the pagefield change for each pivot table separately.
regards
paul








- Show quoted text -

So, Paul, if the routine is pasted on the sheet
where I want the pivot tables to change, I still have
to change ActiveSheet to WorkSheet("MySheet")...?

 

[paul.robinson]

Hi
I was assuming (and so was everyone else I suspect) that your code was
in a general code module. In the VB editor go to Insert, Module. Paste
your code in there and see how it goes.
The code module behind each sheet is only to run code that activates
when something about the sheet changes. As you say, there is no need
to refer to the sheet name under those conditions, but you are only
allowed to run subs with specific names not some general sub.
regards
Paul

 

[Roger Govier]

Hi

Debra's code, from which you started was / should be Sheet code.
It is triggered by Worksheet_Change event.
As you had said that your 2 Pt's and Chart were on one sheet, I had assumed
that you would put the code on that sheet, and that Cell D2 which you are
changing, is on the same sheet.

That being the case, it would be the activeSheet at the point when you
change the value in D2, which would then trigger the changes in the Page
field of each and every PT that exists on that sheet.

 

[Opal]

Hi

Debra's code, from which you started was / should be Sheet code.
It is triggered by Worksheet_Change event.
As you had said that your 2 Pt's and Chart were on one sheet, I had assumed
that you would put the code on that sheet, and that Cell D2 which you are
changing, is on the same sheet.

That being the case, it would be the activeSheet at the point when you
change the value in D2, which would then trigger the changes in the Page
field of each and every PT that exists on that sheet.

--
Regards
Roger Govier










- Show quoted text -

Hi all, thank you for your input and pardon my coding ignorance.
Actually, the situation is a little more involved....I change
a cell on my main sheet, which by code:

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

strField = "Line"

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

If Target.Address = Range("C2").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

Sheets("Report").Select

changes the same page field "Line" in all my other
pivot tables in the workbook. However, several pivot tables
have more than one page field and I need to change these
page fields based on changes to the cell on the pivot table sheet.

Basically, I change the production line field on my first page, it
triggers a change to a pivot table in another worksheet. This
change in the pivot table causes changes to other calculated
cells in the worksheets that once changed, causes changes to the
cells on the pivot table sheets and once these change, I want to
trigger a change to the pivot table page fields. So the line name
changes on the main page, a pivot table calculates frequent
downtime for that line by part number and reason for downtime.
Other pivot tables in other worksheets now need to change
page fields as a result. Clear as mud?

 

[Opal]

So my new code in the module would run something like this:

Sub Worksheet_Change1()

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


strField = "PARTNO"


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


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

For Each pt In Sheets("FSChart1").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


End If

Application.EnableEvents = True
Application.ScreenUpdating = True


End Sub

But for line:

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

I get an error "Variable not defined" and the word "Target"
highlighted...

Should this line be changed as the code is in a separate module and
no longer in the worksheet itself?

 

[Roger Govier]

Hi

If the code is in a standard module, there is no activate event and there
is no Target.
You would need to trigger the macro yourself, by Alt+F8>Select macro>Run or
by assigning the macro to a button and clicking that button.

So
If Target.Address = Range("A1").Address Then
and its corresponding End If would need to be removed.

 

[Opal]

Hi

If the code is in a standard module, there is no activate  event and there
is no Target.
You would need to trigger the macro yourself, by Alt+F8>Select macro>Run or
by assigning the macro to a button and clicking that button.

So
 If Target.Address = Range("A1").Address Then
and its corresponding End If would need to be removed.
--
Regards
Roger Govier



















- Show quoted text -

But A1 is the location of the cell where the data is changed
and the pivot table should change based on the new value
on the A1 cell.... this is ultimately what I want to happen.

 

[Roger Govier]

Hi

Sorry I forgot you weren't a coder.
The full changes you need to make are

Sub Worksheet_Change1()

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

strField = "PARTNO"
newvalue = Range("A1").Value

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

For Each pt In Sheets("FSChart1").PivotTables
With pt.PageFields(strField)
For Each pi In .PivotItems
If pi.Value = newvalue Then
.CurrentPage = newvalue
Exit For
Else
.CurrentPage = "(All)"
End If
Next pi
End With
Next pt
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

 

[Opal]

Thank you, Roger...so this would run in a module or
on the FSChart1 sheet? The A1 cell is in the FSChart1 sheet
so would the lines:

.CurrentPage = newvalue
Exit For
Else
.CurrentPage = "(All)"


Still work as FSChart1 is not the current page....?