Using the same macro on different sheets

[Beverly Darvill]

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

 

[Don Guillett]

As always, post YOUR macro for comments

 

[Beverly Darvill]

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

 

[Don Guillett]

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]

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

 

[Gord Dibben]

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

 

[Beverly Darvill]

Where do I change the code ato run on Activeworkbook and Activesheet please.

 

[Gord Dibben]

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