|
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. |
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. |
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. |
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.... |
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? |
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 |
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 - |
Pivot Table - change page field help
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 |
Pivot Table - change page field help
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 "Opal" wrote in message ... 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 |
Pivot Table - change page field help
On Mar 11, 10:14*am, "Roger Govier"
<roger@technology4unospamdotcodotuk wrote: 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 "Opal" wrote in message ... 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- Hide quoted text - - 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. |
Pivot Table - change page field help
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 On Mar 12, 6:51*pm, Opal wrote: On Mar 11, 10:14*am, "Roger Govier" <roger@technology4unospamdotcodotuk wrote: 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 "Opal" wrote in message .... 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- Hide quoted text - - 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.- Hide quoted text - - Show quoted text - |
Pivot Table - change page field help
On Mar 12, 3:06*pm, wrote:
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 On Mar 12, 6:51*pm, Opal wrote: On Mar 11, 10:14*am, "Roger Govier" <roger@technology4unospamdotcodotuk wrote: 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 "Opal" wrote in message .... 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- Hide quoted text - - 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.- Hide quoted text - - Show quoted text -- Hide quoted text - - 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")...? |
Pivot Table - change page field help
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 On Mar 12, 7:51*pm, Opal wrote: On Mar 12, 3:06*pm, wrote: 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 On Mar 12, 6:51*pm, Opal wrote: On Mar 11, 10:14*am, "Roger Govier" <roger@technology4unospamdotcodotuk wrote: 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 "Opal" wrote in message ... 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- Hide quoted text - - 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.- Hide quoted text - - Show quoted text -- Hide quoted text - - 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")...?- Hide quoted text - - Show quoted text - |
Pivot Table - change page field help
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 "Opal" wrote in message ... On Mar 11, 10:14 am, "Roger Govier" <roger@technology4unospamdotcodotuk wrote: 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 "Opal" wrote in message ... 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- Hide quoted text - - 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. |
Pivot Table - change page field help
On Mar 12, 5:23*pm, "Roger Govier"
<roger@technology4unospamdotcodotuk wrote: 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 "Opal" wrote in message ... On Mar 11, 10:14 am, "Roger Govier" <roger@technology4unospamdotcodotuk wrote: 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 "Opal" wrote in message .... 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- Hide quoted text - - 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.- Hide quoted text - - 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? |
Pivot Table - change page field help
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? |
Pivot Table - change page field help
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+F8Select macroRun 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 "Opal" wrote in message ... 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? |
Pivot Table - change page field help
On Mar 14, 6:01*am, "Roger Govier"
<roger@technology4unospamdotcodotuk wrote: 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+F8Select macroRun 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 "Opal" wrote in message ... 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?- Hide quoted text - - 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. |
Pivot Table - change page field help
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 -- Regards Roger Govier "Opal" wrote in message ... On Mar 14, 6:01 am, "Roger Govier" <roger@technology4unospamdotcodotuk wrote: 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+F8Select macroRun 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 "Opal" wrote in message ... 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?- Hide quoted text - - 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. |
Pivot Table - change page field help
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....? |
Pivot Table - change page field help
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 -- Regards Roger Govier "Opal" wrote in message ... 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....? |
Pivot Table - change page field help
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? |
Pivot Table - change page field help
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 "Opal" wrote in message ... 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? |
Pivot Table - change page field help
On Mar 16, 12:02*pm, "Roger Govier"
<roger@technology4unospamdotcodotuk wrote: 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 "Opal" wrote in message ... 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?- Hide quoted text - - 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. |
| All times are GMT +1. The time now is 11:25 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com