How do go to each worksheet and run a macro?

**felicity26** · 10-26-2006, 05:43 PM

Hello all you gurus

Does anyone know how to do this? The macro I have made to run in one worksheet I now need to run on many worksheets as my workbook has grown.
I don't want it to run on a sheet called index or any sheet that has

"Name" on cell AC3

Thanks all

Felic

**Mallycat** · 10-26-2006, 05:57 PM

You will need to provide more info. How is the macro started ? is it automatic when you open the book or do you instigate it yourself. Somewhere in the macro will be a reference to where it should run (explicit or implied).

by explicit, I mean it actually names the sheet. If this is the case, then the macro will need to be changed to run on other named sheets.

by implied, I mean that it just runs on what ever sheet is selected when the macro is called. If this is the case, you will need to loop the macro to also run on other sheets.

If you provide more info (ie the macro), someone will help you.

Matt

**felicity26** · 10-26-2006, 06:17 PM

Hi,
here is the macro which I have copied bits from this forum recently
It runs from a button on the worksheet (it could do with a clean up at some stage I guess)
IR5:IU5 is a set of formulas that is copied over to the data area and used to format some data to be more manageable.
I hope this helps

F

Sub jmpsort()
Range("U16:Y31").Select
Selection.ClearContents
Range("U16").Select
Range("T16").Select
ActiveWindow.ScrollColumn = 236
Range("IR5:IU5").Select
Selection.Copy
Range("T16").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("T16:W24"), Type:=xlFillDefault
Range("T16:W24").Select
Range("X16").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-2]="""","""",RC[-5])"
Range("X16").Select
Selection.AutoFill Destination:=Range("X16:X24"), Type:=xlFillDefault
Range("X16").Select

Range("W16").Select
Range("P16:X24").Select
Selection.Sort Key1:=Range("R16"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("T16:X24").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Range("P16:X24").Select
Selection.Sort Key1:=Range("P16"), Order1:=xlAscending, Header:=xlGuess _
, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("T16:T24").Select
Selection.ClearContents
With Selection
End With
Range("u65536").End(xlUp).Offset(1, 0).Select
ActiveCell.Value = "End"
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = "End"
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = _
"//*********************************************************************"
End Sub

**Mallycat** · 10-26-2006, 06:45 PM

it could do with a clean up at some stage I guess

I'll say!

The easiest thing I can suggest is to split the macro into 2 macros. Link your button to this new macro, and get the new macro to call the old macro for each sheet you want to run. ie

Please Login or Register  to view this content.

Some tips for you to clean up your code.

This code

Please Login or Register  to view this content.

is the same as this code

Please Login or Register  to view this content.

This code

Please Login or Register  to view this content.

is the same as this code

Please Login or Register  to view this content.

etc

**felicity26** · 10-26-2006, 06:56 PM

Thanks for your help,
A combination of my lack of VBA knowledge and cobbling various recorded and other macros has resulted in the mess I have. I will clean it up

WRT your suggestion, people will add sheets to this workbook from time to time
so I would have to continually go back and modify the macro. So I sort of need a macro that can call up each sheet (that is valid) and run the macro.
Thanks anyway

F

**Mallycat** · 10-26-2006, 07:00 PM

As long as you want the macro to run on ALL sheets, this will do it for you.

add this line after
Sub jmpsort()
for each sheet in worksheets

add this line before "End Sub"

next sheet
end sub