| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table - change page field help
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Pivot Table - change page field help
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 On Mar 10, 2:49*pm, Opal wrote: 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. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Pivot Table - change page field help
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. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Pivot Table - change page field help
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.... |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Pivot Table - change page field help
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? |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Pivot Table - change page field help
On Mar 10, 2:51*pm, Opal wrote:
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?- 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Pivot Table - change page field help
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 ... 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 - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Page field - pivot table | Excel Discussion (Misc queries) | |||
| Pivot Table Page Field | Excel Programming | |||
| Change Page Field in a Pivot Table and Print Chart + Data | Excel Programming | |||
| Pivot Table Page Field | Excel Discussion (Misc queries) | |||
| Pivot Table Page Field | Excel Programming | |||
Hybrid Mode
