Excel/merging macros

Advertisement


Question
Hi Tom

I wonder if the following is possible.

I have recorded six macros which do exactly the same thing.  Each macro uses slightly different cell references.  I am wondering if it is possible to merge all of the macros so that the six different procedures are executed at the same time.

Alternatively, is there a way of calling all of the macros at the same time.

Thanks in advance

Chris Mitchell



Sub rankaut1()
'
' rankaut1 Macro
'

'
   ActiveSheet.Calculate
   Range("A1").Select
  
   Range("EE102:EF136").Select
   Selection.Copy
   
   Range("EE64").Select
   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
       :=False, Transpose:=False
   Application.CutCopyMode = False
   ActiveWorkbook.Worksheets("SPtemplate2").Sort.SortFields.Clear
   ActiveWorkbook.Worksheets("SPtemplate2").Sort.SortFields.Add Key:=Range( _
       "EE64"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
       xlSortNormal
   With ActiveWorkbook.Worksheets("SPtemplate2").Sort
       .SetRange Range("EE64:EF98")
       .Header = xlNo
       .MatchCase = False
       .Orientation = xlTopToBottom
       .SortMethod = xlPinYin
       .Apply
       
       Range("EV322").Select
       
       ActiveSheet.Calculate
   End With
End Sub


Sub rankaut2()
'
' rankaut2 Macro
'

'
   ActiveSheet.Calculate
   Range("A1").Select
  
   Range("EM102:EN135").Select
   Selection.Copy
   ActiveWindow.SmallScroll Down:=-45
   Range("EM64").Select
   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
       :=False, Transpose:=False
   Application.CutCopyMode = False
   ActiveWorkbook.Worksheets("SPtemplate2").Sort.SortFields.Clear
   ActiveWorkbook.Worksheets("SPtemplate2").Sort.SortFields.Add Key:=Range( _
       "EM64"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
       xlSortNormal
   With ActiveWorkbook.Worksheets("SPtemplate2").Sort
       .SetRange Range("EM64:EN97")
       .Header = xlGuess
       .MatchCase = False
       .Orientation = xlTopToBottom
       .SortMethod = xlPinYin
       .Apply
   End With
   
   Range("EN298").Select
   
   ActiveSheet.Calculate
   
End Sub
Sub rankspr1()
'
' rankspr1 Macro
'

'
   ActiveSheet.Calculate
   Range("A1").Select
   
   Range("EU102:EV135").Select
   Selection.Copy
   ActiveWindow.SmallScroll Down:=-45
   Range("EU64").Select
   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
       :=False, Transpose:=False
   Application.CutCopyMode = False
   ActiveWorkbook.Worksheets("SPtemplate2").Sort.SortFields.Clear
   ActiveWorkbook.Worksheets("SPtemplate2").Sort.SortFields.Add Key:=Range( _
       "EU64"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
       xlSortNormal
   With ActiveWorkbook.Worksheets("SPtemplate2").Sort
       .SetRange Range("EU64:EV97")
       .Header = xlNo
       .MatchCase = False
       .Orientation = xlTopToBottom
       .SortMethod = xlPinYin
       .Apply
   End With
   
   Range("FC298").Select
   
   ActiveSheet.Calculate
   
End Sub
Sub rankspr2()
'
' rankspr2 Macro
'

'
   ActiveSheet.Calculate
   
   Range("A1").Select
   
   Range("FC102:FD135").Select
   Selection.Copy
   ActiveWindow.SmallScroll Down:=-42
   Range("FC64").Select
   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
       :=False, Transpose:=False
   Application.CutCopyMode = False
   ActiveWorkbook.Worksheets("SPtemplate2").Sort.SortFields.Clear
   ActiveWorkbook.Worksheets("SPtemplate2").Sort.SortFields.Add Key:=Range( _
       "FC64"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
       xlSortNormal
   With ActiveWorkbook.Worksheets("SPtemplate2").Sort
       .SetRange Range("FC64:FD97")
       .Header = xlNo
       .MatchCase = False
       .Orientation = xlTopToBottom
       .SortMethod = xlPinYin
       .Apply
   End With
  
   Range("FD298").Select
   
   ActiveSheet.Calculate
   
End Sub
Sub ranksum1()
'
' ranksum1 Macro
'

'
   ActiveSheet.Calculate
   
   Range("A1").Select
  
   Range("FK102:FL135").Select
   Selection.Copy
   ActiveWindow.SmallScroll Down:=-51
   Range("FK64").Select
   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
       :=False, Transpose:=False
   Application.CutCopyMode = False
   ActiveWorkbook.Worksheets("SPtemplate2").Sort.SortFields.Clear
   ActiveWorkbook.Worksheets("SPtemplate2").Sort.SortFields.Add Key:=Range( _
       "FK64"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
       xlSortNormal
   With ActiveWorkbook.Worksheets("SPtemplate2").Sort
       .SetRange Range("FK64:FL97")
       .Header = xlGuess
       .MatchCase = False
       .Orientation = xlTopToBottom
       .SortMethod = xlPinYin
       .Apply
   End With
  
   Range("FL298").Select
   
   ActiveSheet.Calculate
   
End Sub


Sub ranksum2()
'
' ranksum1 Macro
'

'
   ActiveSheet.Calculate
   
   Range("A1").Select
  
   Range("FS102:FT135").Select
   Selection.Copy
   ActiveWindow.SmallScroll Down:=-51
   Range("FS64").Select
   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
       :=False, Transpose:=False
   Application.CutCopyMode = False
   ActiveWorkbook.Worksheets("SPtemplate2").Sort.SortFields.Clear
   ActiveWorkbook.Worksheets("SPtemplate2").Sort.SortFields.Add Key:=Range( _
       "FS64"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
       xlSortNormal
   With ActiveWorkbook.Worksheets("SPtemplate2").Sort
       .SetRange Range("FS64:FT97")
       .Header = xlGuess
       .MatchCase = False
       .Orientation = xlTopToBottom
       .SortMethod = xlPinYin
       .Apply
   End With
  
   Range("FS298").Select
   
   ActiveSheet.Calculate
   
End Sub

Answer
Christopher,

You can call them

Sub Master()
rankaut1
rankaut2
rankspr1
rankspr2
ranksum1
ranksum2
End Sub

or you can just copy and paste all the code in another " master" macro - one set of code after the other - and then just run that master macro.

I am just answering from a mechanical standpoint - I have no idea what you code is trying to achieve and whether that makes sense or not.

--
Regards,
Tom Ogilvy

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.