Run A Macro On Selected Worksheets

[carl]

Is there a way I can have this run on all selected worksheets ?

Sub Rank()

ActiveCell.Select
Selection.End(xlToRight).Select

ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=RANK(RC[-8],R10C2:R600C2)"
Selection.Copy
ActiveCell.Offset(0, -1).Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Range("A1").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveCell.Offset(-475, 0).Range("A1:A476").Select
ActiveCell.Activate
ActiveSheet.Paste
Application.CutCopyMode = False
Calculate

End Sub

Thank you in advance

 

[Richard Buttrey]

You could put the code in the workbooks

Private Sub Workbook_SheetBeforeDoubleClick

event.

It would then trigger every time you double clicked a cell


HTH

Is there a way I can have this run on all selected worksheets ?

Sub Rank()

ActiveCell.Select
Selection.End(xlToRight).Select

ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=RANK(RC[-8],R10C2:R600C2)"
Selection.Copy
ActiveCell.Offset(0, -1).Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Range("A1").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveCell.Offset(-475, 0).Range("A1:A476").Select
ActiveCell.Activate
ActiveSheet.Paste
Application.CutCopyMode = False
Calculate

End Sub

Thank you in advance

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

 

[Jim Thomlinson]

You can give this a try...

Sub Rank()
Dim wks As Worksheet

For Each wks In ActiveWindow.SelectedSheets
wks.Select
ActiveCell.Select
Selection.End(xlToRight).Select

ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=RANK(RC[-8],R10C2:R600C2)"
Selection.Copy
ActiveCell.Offset(0, -1).Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Range("A1").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveCell.Offset(-475, 0).Range("A1:A476").Select
ActiveCell.Activate
ActiveSheet.Paste
Application.CutCopyMode = False
Next wks
Calculate

End Sub