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