| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Run A Macro On Selected Worksheets
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Run A Macro On Selected Worksheets
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 On Thu, 31 Aug 2006 09:35:01 -0700, carl wrote: 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 __________________________ |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Run A Macro On Selected Worksheets
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 -- HTH... Jim Thomlinson "carl" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Saving worksheets using a Macro | Excel Discussion (Misc queries) | |||
| Macro to start when cell selected | Excel Discussion (Misc queries) | |||
| Running a macro in a selected cell... | Excel Discussion (Misc queries) | |||
| Modify a Macro to Repeat for all Selected Worksheets | Excel Worksheet Functions | |||
| Repeating a Macro For Each Selected Worksheet | Excel Worksheet Functions | |||
Linear Mode
