(Msg. 1) Posted: Wed Aug 20, 2008 6:19 am
Post subject: Using the same macro on different sheets Archived from groups: microsoft>public>excel>worksheet>functions (more info?)
I have a number of worksheets that employ the same macro and on occasions I
need to update the information with the macro. To stop having to repeat the
information a number of times I want to store all the macros in one sheet so
that I only have to update the information once. However I don't want the
sheet that holds the macro to open everytime and then have to close it
everytime. Is this possible? If so how
(Msg. 2) Posted: Wed Aug 20, 2008 6:38 am
Post subject: RE: Using the same macro on different sheets [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
Sub Project_FilterOn()
'
' FilterOn Macro
' Macro recorded 19/03/2008 by Beverly Darvill
'
'
Range("E2").Select
Sheets("Resource Groups").Select
Selection.AutoFilter Field:=29, Criteria1:="<>0", Operator:=xlAnd
Sheets("Project").Select
Range("C2").Select
Selection.AutoFilter Field:=27, Criteria1:="<>0", Operator:=xlAnd
Range("C2").Select
Sheets("Resource Groups").Select
Range("E2").Select
End Sub
I would be changing the field number but I have a number of macros that I
need to change the number field that are used across similar sheets
"Beverly Darvill" wrote:
> I have a number of worksheets that employ the same macro and on occasions I
> need to update the information with the macro. To stop having to repeat the
> information a number of times I want to store all the macros in one sheet so
> that I only have to update the information once. However I don't want the
> sheet that holds the macro to open everytime and then have to close it
> everytime. Is this possible? If so how
>
> Thanks Beverly
(Msg. 3) Posted: Wed Aug 20, 2008 8:27 am
Post subject: Re: Using the same macro on different sheets [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
As always, post YOUR macro for comments
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1 DeleteThis @austin.rr.com
"Beverly Darvill" <beverlypdarvill DeleteThis @eaton.com> wrote in message
news:66E3410C-A205-4EE9-950B-D0935690E58E@microsoft.com...
>I have a number of worksheets that employ the same macro and on occasions I
> need to update the information with the macro. To stop having to repeat
> the
> information a number of times I want to store all the macros in one sheet
> so
> that I only have to update the information once. However I don't want the
> sheet that holds the macro to open everytime and then have to close it
> everytime. Is this possible? If so how
>
> Thanks Beverly
(Msg. 4) Posted: Wed Aug 20, 2008 9:21 am
Post subject: Re: Using the same macro on different sheets [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
Untested but how about a list of sheets and field number
a 1
v 27
for each c in range("a2:a22")
sheets(c).AutoFilter Field:=" & c.offset(,1) & ", Criteria1:="<>0"
next c
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1.TakeThisOut@austin.rr.com
"Beverly Darvill" <beverlypdarvill.TakeThisOut@eaton.com> wrote in message
news:DB3C6744-3F3C-41BB-94E9-6110B48402F9@microsoft.com...
> Sub Project_FilterOn()
> '
> ' FilterOn Macro
> ' Macro recorded 19/03/2008 by Beverly Darvill
> '
>
> '
> Range("E2").Select
> Sheets("Resource Groups").Select
> Selection.AutoFilter Field:=29, Criteria1:="<>0", Operator:=xlAnd
> Sheets("Project").Select
> Range("C2").Select
> Selection.AutoFilter Field:=27, Criteria1:="<>0", Operator:=xlAnd
> Range("C2").Select
> Sheets("Resource Groups").Select
> Range("E2").Select
> End Sub
>
> I would be changing the field number but I have a number of macros that I
> need to change the number field that are used across similar sheets
>
> "Beverly Darvill" wrote:
>
>> I have a number of worksheets that employ the same macro and on occasions
>> I
>> need to update the information with the macro. To stop having to repeat
>> the
>> information a number of times I want to store all the macros in one sheet
>> so
>> that I only have to update the information once. However I don't want
>> the
>> sheet that holds the macro to open everytime and then have to close it
>> everytime. Is this possible? If so how
>>
>> Thanks Beverly
(Msg. 5) Posted: Wed Aug 20, 2008 9:52 am
Post subject: Re: Using the same macro on different sheets [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
tested
Option Explicit
Sub filtershts()
Dim lr, mf As Long
Dim c As Range
lr = Cells(Rows.Count, "a").End(xlUp).Row
For Each c In Range("a2:a" & lr)
mf = c.Offset(, 1)
Sheets(CStr(c)).Range("a1:d21").AutoFilter Field:=mf, Criteria1:="<>0"
' MsgBox c
Next c
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1.DeleteThis@austin.rr.com
"Don Guillett" <dguillett1.DeleteThis@austin.rr.com> wrote in message
news:uiWJRAtAJHA.908@TK2MSFTNGP03.phx.gbl...
> Untested but how about a list of sheets and field number
> a 1
> v 27
>
> for each c in range("a2:a22")
> sheets(c).AutoFilter Field:=" & c.offset(,1) & ", Criteria1:="<>0"
> next c
> End Sub
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguillett1.DeleteThis@austin.rr.com
> "Beverly Darvill" <beverlypdarvill.DeleteThis@eaton.com> wrote in message
> news:DB3C6744-3F3C-41BB-94E9-6110B48402F9@microsoft.com...
>> Sub Project_FilterOn()
>> '
>> ' FilterOn Macro
>> ' Macro recorded 19/03/2008 by Beverly Darvill
>> '
>>
>> '
>> Range("E2").Select
>> Sheets("Resource Groups").Select
>> Selection.AutoFilter Field:=29, Criteria1:="<>0", Operator:=xlAnd
>> Sheets("Project").Select
>> Range("C2").Select
>> Selection.AutoFilter Field:=27, Criteria1:="<>0", Operator:=xlAnd
>> Range("C2").Select
>> Sheets("Resource Groups").Select
>> Range("E2").Select
>> End Sub
>>
>> I would be changing the field number but I have a number of macros that I
>> need to change the number field that are used across similar sheets
>>
>> "Beverly Darvill" wrote:
>>
>>> I have a number of worksheets that employ the same macro and on
>>> occasions I
>>> need to update the information with the macro. To stop having to repeat
>>> the
>>> information a number of times I want to store all the macros in one
>>> sheet so
>>> that I only have to update the information once. However I don't want
>>> the
>>> sheet that holds the macro to open everytime and then have to close it
>>> everytime. Is this possible? If so how
>>>
>>> Thanks Beverly
>
(Msg. 6) Posted: Wed Aug 20, 2008 2:01 pm
Post subject: Re: Using the same macro on different sheets [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
Place your macro(s) in your Personal.xls
Change the code to run on your Activeworkbook and Activesheet.
Personal .xls will open with each start of Excel.
Mark it as "hidden" under Window>Hide then save it in that condition.
All macros will be available for all opened workbooks.
BTW........I think you have workbooks and worksheets confused.
Worksbooks are comprised of one or more sheets. Sheets cannot be opened by
themseleves.
Gord Dibben MS Excel MVP
On Wed, 20 Aug 2008 06:19:02 -0700, Beverly Darvill
<beverlypdarvill.TakeThisOut@eaton.com> wrote:
>I have a number of worksheets that employ the same macro and on occasions I
>need to update the information with the macro. To stop having to repeat the
>information a number of times I want to store all the macros in one sheet so
>that I only have to update the information once. However I don't want the
>sheet that holds the macro to open everytime and then have to close it
>everytime. Is this possible? If so how
>
>Thanks Beverly
(Msg. 7) Posted: Thu Aug 21, 2008 12:20 am
Post subject: Re: Using the same macro on different sheets [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
Where do I change the code ato run on Activeworkbook and Activesheet please.
"Gord Dibben" wrote:
> Place your macro(s) in your Personal.xls
>
> Change the code to run on your Activeworkbook and Activesheet.
>
> Personal .xls will open with each start of Excel.
>
> Mark it as "hidden" under Window>Hide then save it in that condition.
>
> All macros will be available for all opened workbooks.
>
> BTW........I think you have workbooks and worksheets confused.
>
> Worksbooks are comprised of one or more sheets. Sheets cannot be opened by
> themseleves.
>
>
> Gord Dibben MS Excel MVP
>
>
>
> On Wed, 20 Aug 2008 06:19:02 -0700, Beverly Darvill
> <beverlypdarvill.TakeThisOut@eaton.com> wrote:
>
> >I have a number of worksheets that employ the same macro and on occasions I
> >need to update the information with the macro. To stop having to repeat the
> >information a number of times I want to store all the macros in one sheet so
> >that I only have to update the information once. However I don't want the
> >sheet that holds the macro to open everytime and then have to close it
> >everytime. Is this possible? If so how
> >
> >Thanks Beverly
>
>
(Msg. 8) Posted: Thu Aug 21, 2008 10:26 am
Post subject: Re: Using the same macro on different sheets [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
Post your macro code and we'll have a look.
But.............anywhere you have a hard-coded reference to a sheet or
workbook you would change the code.
i.e. Sheets("Sheet1") would become ActiveSheet if Sheet1 was selected.
Gord
On Thu, 21 Aug 2008 00:20:01 -0700, Beverly Darvill
<beverlypdarvill RemoveThis @eaton.com> wrote:
>Where do I change the code ato run on Activeworkbook and Activesheet please.
>
>"Gord Dibben" wrote:
>
>> Place your macro(s) in your Personal.xls
>>
>> Change the code to run on your Activeworkbook and Activesheet.
>>
>> Personal .xls will open with each start of Excel.
>>
>> Mark it as "hidden" under Window>Hide then save it in that condition.
>>
>> All macros will be available for all opened workbooks.
>>
>> BTW........I think you have workbooks and worksheets confused.
>>
>> Worksbooks are comprised of one or more sheets. Sheets cannot be opened by
>> themseleves.
>>
>>
>> Gord Dibben MS Excel MVP
>>
>>
>>
>> On Wed, 20 Aug 2008 06:19:02 -0700, Beverly Darvill
>> <beverlypdarvill RemoveThis @eaton.com> wrote:
>>
>> >I have a number of worksheets that employ the same macro and on occasions I
>> >need to update the information with the macro. To stop having to repeat the
>> >information a number of times I want to store all the macros in one sheet so
>> >that I only have to update the information once. However I don't want the
>> >sheet that holds the macro to open everytime and then have to close it
>> >everytime. Is this possible? If so how
>> >
>> >Thanks Beverly
>>
>>
All times are: Eastern Time (US & Canada) (change)
Page 1 of 1
You can post new topics in this forum You can reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
Home
Forums
Home
FAQ
Profile
Private Messages
Log in
Office