Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Macros for pivot tables
I am trying to create a macro so that when a selection is made on the drop
down menu, it would apply for all the other pivot tables. While I found great examples in http://www.contextures.com (great site by the way) I have no idea how to create the macro or modify it to meet the my needs. Is there a good book or reference to start learning Excel macros? Thanks, PJS |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Macros for pivot tables
If you'd point us to the specific macro (or copy and paste it here) we can
probably help you with getting it into your workbook, and possibly even customizing it if it needs that. But to "create" it, all you probably need to do is: 1) Open Excel 2) Press [Alt]+[F11] to open the Visual Basic Editor (VBE) 3) In the VBE, choose Insert -- Module 4) Copy the code provided at Contextures and paste it into the empty module presented to you. 5) Close the VBE and use Tools -- Macro -- Macros to run the macro. Now, if it is a macro that begins with Private Sub Workbook_... or Private Sub Worksheet_... The way to get it into your workbook is a bit different, that's why it will help to see the macro itself. There are a number of good sources for beginning to learn VBA for Excel. Here is a list of just a few: Learning VBA there are a number of site around the net to help. http://www.mvps.org/dmcritchie/excel/getstarted.htm http://www.the-excel-advisor.com/exc...-tutorial.html http://class.et.byu.edu/ce270/vbaexcel_primer/intro.htm http://www.exceltip.com/excel_links.html there are other sites that provide usefull information about specific issues. http://www.contextures.com/ http://www.cpearson.com/ http://www.j-walk.com/ http://www.mcgimpsey.com/ http://www.rondebruin.nl/ http://www.mrexcel.com http://www.mvps.org/ Some stuff I have on my site might even help, try this link: http://www.jlathamsite.com Start by clicking the "Learning Office Apps" link. On the page that takes you to are more detailed instructions on how to insert various types of code into your workbook, or into the Workbook/Worksheet event areas (those special cases I mentioned earlier). Also, there is the beginning of my own meager attempt at providing some guidance in VBA programming - very much still a work in progress, but some of it may be useful to you. The link to "Programming In Excel VBA - An Introduction" is associated with a .pdf file you can download. "PJS" wrote: I am trying to create a macro so that when a selection is made on the drop down menu, it would apply for all the other pivot tables. While I found great examples in http://www.contextures.com (great site by the way) I have no idea how to create the macro or modify it to meet the my needs. Is there a good book or reference to start learning Excel macros? Thanks, PJS |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Macros for pivot tables
Thanks for the information! JLatham.
I will check out your site and the links you provided ^_^ I modified the macro and got it to work but I was curious as to what the Private Sub was all about.... Here is the macro. Option Explicit Dim mvPivotPageValue1 As Variant Dim mvPivotPageValue2 As Variant Dim mvPivotPageValue3 As Variant Dim mvPivotPageValue4 As Variant Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable) ''based on code by Robert Rosenberg posted 2000/01/11 ''A module level variable (mvPivotPageValue) keeps ''track of the last selection from the Page Field. Dim wsOther As Worksheet Dim pt As PivotTable Dim pt1 As PivotTable Dim pt2 As PivotTable Dim pf1 As PivotField Dim pf2 As PivotField Dim strField1 As String Dim strField2 As String Dim strField3 As String Dim StrField4 As String strField1 = "Ops_Dir" strField2 = "Chart_Office" strField3 = "Office_Code" StrField4 = "Type" Set wsOther = Sheets("Selection") Set pt = Target Set pt1 = wsOther.PivotTables(1) Set pt2 = wsOther.PivotTables(2) Set pf1 = pt.PivotFields(strField1) Set pf2 = pt.PivotFields(strField2) On Error Resume Next If LCase(pt.PivotFields(strField1).CurrentPage) < LCase(mvPivotPageValue1) Then 'The PageField1 was changed Application.EnableEvents = False pt.RefreshTable mvPivotPageValue1 = pt.PivotFields(strField1).CurrentPage pt1.PageFields(strField1).CurrentPage = mvPivotPageValue1 pt2.PageFields(strField1).CurrentPage = mvPivotPageValue1 Application.EnableEvents = True End If If LCase(pt.PivotFields(strField2).CurrentPage) < LCase(mvPivotPageValue2) Then 'The PageField2 was changed Application.EnableEvents = False pt.RefreshTable mvPivotPageValue2 = pt.PivotFields(strField2).CurrentPage pt1.PageFields(strField2).CurrentPage = mvPivotPageValue2 pt2.PageFields(strField2).CurrentPage = mvPivotPageValue2 Application.EnableEvents = True End If If LCase(pt.PivotFields(strField3).CurrentPage) < LCase(mvPivotPageValue3) Then 'The PageField3 was changed Application.EnableEvents = False pt.RefreshTable mvPivotPageValue3 = pt.PivotFields(strField3).CurrentPage pt1.PageFields(strField3).CurrentPage = mvPivotPageValue3 pt2.PageFields(strField3).CurrentPage = mvPivotPageValue3 Application.EnableEvents = True End If If LCase(pt.PivotFields(StrField4).CurrentPage) < LCase(mvPivotPageValue4) Then 'The PageField4 was changed Application.EnableEvents = False pt.RefreshTable mvPivotPageValue4 = pt.PivotFields(StrField4).CurrentPage pt1.PageFields(StrField4).CurrentPage = mvPivotPageValue4 pt2.PageFields(StrField4).CurrentPage = mvPivotPageValue4 Application.EnableEvents = True End If End Sub "JLatham" wrote: If you'd point us to the specific macro (or copy and paste it here) we can probably help you with getting it into your workbook, and possibly even customizing it if it needs that. But to "create" it, all you probably need to do is: 1) Open Excel 2) Press [Alt]+[F11] to open the Visual Basic Editor (VBE) 3) In the VBE, choose Insert -- Module 4) Copy the code provided at Contextures and paste it into the empty module presented to you. 5) Close the VBE and use Tools -- Macro -- Macros to run the macro. Now, if it is a macro that begins with Private Sub Workbook_... or Private Sub Worksheet_... The way to get it into your workbook is a bit different, that's why it will help to see the macro itself. There are a number of good sources for beginning to learn VBA for Excel. Here is a list of just a few: Learning VBA there are a number of site around the net to help. http://www.mvps.org/dmcritchie/excel/getstarted.htm http://www.the-excel-advisor.com/exc...-tutorial.html http://class.et.byu.edu/ce270/vbaexcel_primer/intro.htm http://www.exceltip.com/excel_links.html there are other sites that provide usefull information about specific issues. http://www.contextures.com/ http://www.cpearson.com/ http://www.j-walk.com/ http://www.mcgimpsey.com/ http://www.rondebruin.nl/ http://www.mrexcel.com http://www.mvps.org/ Some stuff I have on my site might even help, try this link: http://www.jlathamsite.com Start by clicking the "Learning Office Apps" link. On the page that takes you to are more detailed instructions on how to insert various types of code into your workbook, or into the Workbook/Worksheet event areas (those special cases I mentioned earlier). Also, there is the beginning of my own meager attempt at providing some guidance in VBA programming - very much still a work in progress, but some of it may be useful to you. The link to "Programming In Excel VBA - An Introduction" is associated with a .pdf file you can download. "PJS" wrote: I am trying to create a macro so that when a selection is made on the drop down menu, it would apply for all the other pivot tables. While I found great examples in http://www.contextures.com (great site by the way) I have no idea how to create the macro or modify it to meet the my needs. Is there a good book or reference to start learning Excel macros? Thanks, PJS |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Macros for pivot tables
When a sub is "Private" two things take place (or don't take place, depending
on how you look at it). First, the Sub will not show up in the list of macros available to be used from Tools -- Macro -- Macros, and second, it cannot be referenced or used by code in any other code modules, just the one it is in. All Subs that are attached to workbook/worksheet events, such as the Worksheet's PivotTableUpdate event are private by default. "PJS" wrote: Thanks for the information! JLatham. I will check out your site and the links you provided ^_^ I modified the macro and got it to work but I was curious as to what the Private Sub was all about.... Here is the macro. Option Explicit Dim mvPivotPageValue1 As Variant Dim mvPivotPageValue2 As Variant Dim mvPivotPageValue3 As Variant Dim mvPivotPageValue4 As Variant Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable) ''based on code by Robert Rosenberg posted 2000/01/11 ''A module level variable (mvPivotPageValue) keeps ''track of the last selection from the Page Field. Dim wsOther As Worksheet Dim pt As PivotTable Dim pt1 As PivotTable Dim pt2 As PivotTable Dim pf1 As PivotField Dim pf2 As PivotField Dim strField1 As String Dim strField2 As String Dim strField3 As String Dim StrField4 As String strField1 = "Ops_Dir" strField2 = "Chart_Office" strField3 = "Office_Code" StrField4 = "Type" Set wsOther = Sheets("Selection") Set pt = Target Set pt1 = wsOther.PivotTables(1) Set pt2 = wsOther.PivotTables(2) Set pf1 = pt.PivotFields(strField1) Set pf2 = pt.PivotFields(strField2) On Error Resume Next If LCase(pt.PivotFields(strField1).CurrentPage) < LCase(mvPivotPageValue1) Then 'The PageField1 was changed Application.EnableEvents = False pt.RefreshTable mvPivotPageValue1 = pt.PivotFields(strField1).CurrentPage pt1.PageFields(strField1).CurrentPage = mvPivotPageValue1 pt2.PageFields(strField1).CurrentPage = mvPivotPageValue1 Application.EnableEvents = True End If If LCase(pt.PivotFields(strField2).CurrentPage) < LCase(mvPivotPageValue2) Then 'The PageField2 was changed Application.EnableEvents = False pt.RefreshTable mvPivotPageValue2 = pt.PivotFields(strField2).CurrentPage pt1.PageFields(strField2).CurrentPage = mvPivotPageValue2 pt2.PageFields(strField2).CurrentPage = mvPivotPageValue2 Application.EnableEvents = True End If If LCase(pt.PivotFields(strField3).CurrentPage) < LCase(mvPivotPageValue3) Then 'The PageField3 was changed Application.EnableEvents = False pt.RefreshTable mvPivotPageValue3 = pt.PivotFields(strField3).CurrentPage pt1.PageFields(strField3).CurrentPage = mvPivotPageValue3 pt2.PageFields(strField3).CurrentPage = mvPivotPageValue3 Application.EnableEvents = True End If If LCase(pt.PivotFields(StrField4).CurrentPage) < LCase(mvPivotPageValue4) Then 'The PageField4 was changed Application.EnableEvents = False pt.RefreshTable mvPivotPageValue4 = pt.PivotFields(StrField4).CurrentPage pt1.PageFields(StrField4).CurrentPage = mvPivotPageValue4 pt2.PageFields(StrField4).CurrentPage = mvPivotPageValue4 Application.EnableEvents = True End If End Sub "JLatham" wrote: If you'd point us to the specific macro (or copy and paste it here) we can probably help you with getting it into your workbook, and possibly even customizing it if it needs that. But to "create" it, all you probably need to do is: 1) Open Excel 2) Press [Alt]+[F11] to open the Visual Basic Editor (VBE) 3) In the VBE, choose Insert -- Module 4) Copy the code provided at Contextures and paste it into the empty module presented to you. 5) Close the VBE and use Tools -- Macro -- Macros to run the macro. Now, if it is a macro that begins with Private Sub Workbook_... or Private Sub Worksheet_... The way to get it into your workbook is a bit different, that's why it will help to see the macro itself. There are a number of good sources for beginning to learn VBA for Excel. Here is a list of just a few: Learning VBA there are a number of site around the net to help. http://www.mvps.org/dmcritchie/excel/getstarted.htm http://www.the-excel-advisor.com/exc...-tutorial.html http://class.et.byu.edu/ce270/vbaexcel_primer/intro.htm http://www.exceltip.com/excel_links.html there are other sites that provide usefull information about specific issues. http://www.contextures.com/ http://www.cpearson.com/ http://www.j-walk.com/ http://www.mcgimpsey.com/ http://www.rondebruin.nl/ http://www.mrexcel.com http://www.mvps.org/ Some stuff I have on my site might even help, try this link: http://www.jlathamsite.com Start by clicking the "Learning Office Apps" link. On the page that takes you to are more detailed instructions on how to insert various types of code into your workbook, or into the Workbook/Worksheet event areas (those special cases I mentioned earlier). Also, there is the beginning of my own meager attempt at providing some guidance in VBA programming - very much still a work in progress, but some of it may be useful to you. The link to "Programming In Excel VBA - An Introduction" is associated with a .pdf file you can download. "PJS" wrote: I am trying to create a macro so that when a selection is made on the drop down menu, it would apply for all the other pivot tables. While I found great examples in http://www.contextures.com (great site by the way) I have no idea how to create the macro or modify it to meet the my needs. Is there a good book or reference to start learning Excel macros? Thanks, PJS |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Building pivot tables in Excel 2007 based on existing pivot tables? | Excel Discussion (Misc queries) | |||
Macros / VBA / Pivot Tables in 2003 & 2007 | Setting up and Configuration of Excel | |||
Macros/Protection/Pivot Tables...HELP | Excel Discussion (Misc queries) | |||
Pivot tables and macros | Excel Discussion (Misc queries) | |||
What's the best way to learn Pivot Tables and using Macros in Exc. | New Users to Excel |