Using the same macro on different sheets
 

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

Using the same macro on different sheets
 

As always, post YOUR macro for comments


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Beverly Darvill" wrote in message
...
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

Using the same macro on different sheets
 

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

Using the same macro on different sheets
 

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

"Beverly Darvill" wrote in message
...
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

Using the same macro on different sheets
 

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

"Don Guillett" wrote in message
...
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

"Beverly Darvill" wrote in message
...
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

Using the same macro on different sheets
 

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 WindowHide 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
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

Using the same macro on different sheets
 

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 WindowHide 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
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

Using the same macro on different sheets
 

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
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 WindowHide 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
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