Running all my Macros In excel instead of one by one

Question

I wrote a few small Macros since I am not very good at this and all of them work however I would like to run all of these at once or in order as they are written. I tried to use a call but this didnt work for some reason. Basically what I have done is some basic formatting for a file so it can be pasted into another file. Attached is the macros I have so far. I exported this into a text file to share and I am importing the file into excel so it can be used on any file by anyone who does this but I need the macros to all run instead of one by one. Any advice? Thank you so much.

Sub RemoveHyperlink()
' ' RemoveHyperlink Macro '

    '
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Hyperlinks.Delete
    End Sub
Sub RemoveFill()

' ' RemoveFill Macro '

    '
    Range("A1:G604").Select
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Range("C3").Select
End Sub

Sub RemoveBold()

' ' RemoveBold Macro '

    '
    Cells.Select
    Selection.Font.Bold = True
    Selection.Font.Bold = False
End Sub

Sub RemoveBoarders()

' ' RemoveBoarders Macro '

    Cells.Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
End Sub

Sub FreezePanes()

' ' FreezePanes Macro '

    Rows("12:12").Select
    ActiveWindow.FreezePanes = True
    Range("K21").Select
End Sub

Sub DeleteColumn_C()

' ' DeleteColumn_C Macro '

    Columns("C:C").Select
    Selection.Delete Shift:=xlToLeft
    Range("J22").Select
End Sub

Sub SortData()
' ' SortData Macro '

    '
    Range("A11:G1048576").Select
    Range("G1048576").Activate
    ActiveWorkbook.Worksheets("FNDWRR").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("FNDWRR").Sort.SortFields.Add2 Key:=Range("A12:A604"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= xlSortNormal
    With ActiveWorkbook.Worksheets("FNDWRR").Sort
        .SetRange Range("A11:G604")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("J28").Select
End Sub

Sub InsertColums_5()

' ' InsertColums_5 Macro '

    '
    Columns("D:H").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove End Sub Sub TextToColumns() ' '
    TextToColumns Macro                          '

    '
    ActiveWindow.SmallScroll Down:=-9
    Range("C12").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.TextToColumns Destination:=Range("C12"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=True, Other:=True, OtherChar:= _
        ".", FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 2), Array(5, 2), _
                              Array(6, 2)), TrailingMinusNumbers:=True
End Sub

Sub CopyAccountsto_A()
' ' CopyAccountsto_A Macro '

    '
    ActiveWindow.SmallScroll Down:=-6
    Range("D12").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Range("A12").Select
    ActiveSheet.Paste
    Range("D7").Select
End Sub

Sub DeleteColumn_D()
' ' DeleteColumn_D Macro '

    '
    Columns("D:D").Select
    Selection.Delete Shift:=xlToLeft
    Range("D8").Select End Sub Sub AddTextToHeaders() ' ' AddTextToHeaders Macro '

    '
    Range("C11").Select
    ActiveCell.FormulaR1C1 = "DIV"
    Range("D11").Select
    ActiveCell.FormulaR1C1 = "CCTR"
    Range("E11").Select
    ActiveCell.FormulaR1C1 = "Proj"
    Range("F11").Select
    ActiveCell.FormulaR1C1 = "Fut"
    Range("G11").Select
    ActiveCell.FormulaR1C1 = "I/C"
    Range("G10").Select
End Sub

Sub AddGridlines()
' ' AddGridlines Macro '

    '
    ActiveWindow.DisplayGridlines = True

End Sub

Sub RunOrder()

    Call RemoveHyperlink
    Call RemoveFill
    Call RemoveBold
    Call ReomveBoarders
    Call FreezePanes
    Call DeleteColumn_C
    Call SortData
    Call InsertColumns_5
    Call TextToColumns
    Call CopyAccountsto_A
    Call DeleteColumn_D
    Call AddTextToHeaders
    Call AddGridlines

End Sub

You really want to [avoid using select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) in your code. Indenting it properly will also greatly add clarity to it, [Rubberduck](http://rubberduckvba.com/Indentation) can really help with this. If you remove all the `Sub` / `End sub`, you will end up with all lines of code running in that order. — cybernetic.nomad, Nov 15 '19 at 19:45
I just want to be able to run all the Macros in the order they are listed — Christopher J Rode, Nov 15 '19 at 19:49
@cybernetic.nomad please continue to fix two SUB-s alignment, tkx — donPablo, Nov 15 '19 at 19:51
So there are 13 sub-macros, each being "called". The problem sez "call but this didnt work for some reason" and we are left to guess what that reason might be. Please share with us which macro it failed in, and the exact reason for failure. Tell us what you see. — donPablo, Nov 15 '19 at 19:52
as @cybernetic.nomad suggested remove all of the `Sub` `End Sub` after `Sub RemoveHyperlink()` except leave `End Sub` at the very end and dont worry about using the `Sub RunOrder()`. Just run `Sub RemoveHyperlink()`. You will want to change that Subs name of course. — Zack E, Nov 15 '19 at 19:57
if you step through the `Sub RunOrder()` where does it fail? — Zack E, Nov 15 '19 at 20:04
All of the macros work correctly for me. When I tried to put the call in here it says the "Sub Run Order()" is not defined and I cannot get them to run as one — Christopher J Rode, Nov 15 '19 at 20:06
I am just looking for a way to have all my macros run at one time in order rather than having to click in one by one — Christopher J Rode, Nov 15 '19 at 20:07
No I do not. The way it is pasted above is how it in the excel — Christopher J Rode, Nov 15 '19 at 20:14
You said: *It says the "Sub RunOrder()" is not defined* Maybe you misinterpreted what you saw on screen. You saw a dialog with the message: *Compile error: Sub or Function not defined* You pressed the OK button and `Sub RunOrder()` became highlighted yellow and `Call ReomveBoarders` was highlighted blue. The yellow highlight indicates where processing stopped and the blue highlight where an error is detected. IOW, The error in this case is not that `Sub RunOrder()` is not defined, but it is that `ReomveBoarders` is not defined (due to the wrong spelling). — Tom Brunberg, Nov 15 '19 at 21:06
@TomBrunberg: If `Option Explicit` is not included, then the error message will only point to the macro call in question. Adding `Option Explicit` will enable debugging which will then usually point to the offending line. I say 'usually' because when a macro calls a class, the debugger does not usually step into the class and that has to be investigated manually. — AJD, Nov 15 '19 at 21:20
@AJD I'm using Office 2007, maybe that is different from what you use. I had no `Option Explicit` and the error indication was as I described above. Actually, adding Opt Exp made no difference, the error message and highlights are just the same. Anyway, my point was just to speculate why OP saw the messages he said that he saw. — Tom Brunberg, Nov 15 '19 at 21:30

score 3 · Answer 1 · answered Nov 15 '19 at 20:54

Always. Always. Always. Put Option Explicit at the top of modules.

IF you had done so, you would have been provided with some additional information and then a line would have been highlighted:

Sub RunOrder()
    RemoveHyperlink
    RemoveFill
    RemoveBold
    ReomveBoarders ' <-- Typing error
    FreezePanes
    DeleteColumn_C
    SortData
    InsertColumns_5 ' <--- typing error
    TextToColumns
    CopyAccountsto_A
    DeleteColumn_D
    AddTextToHeaders
    AddGridlines
End Sub

While not essential to the answer, the keyword Call is a hangover from very old VB syntax and is not required. At one stage MS marked this keyword as deprecated although I cannot find the reference these days. Code reads better without it.

The key point, and the solution to your question is:

Always. Always. Always. Put Option Explicit at the top of modules.

score 1 · Answer 2 · answered Nov 15 '19 at 20:27

It didnt work because we have some mistake on the names:

Sub RunOrder()

Call RemoveHyperlink
Call RemoveFill
Call RemoveBold
Call RemoveBoarders # not ReomveBoarders
Call FreezePanes
Call DeleteColumn_C
Call SortData
Call InsertColums_5 # and not InsertColumns_5
Call TextToColumns
Call CopyAccountsto_A
Call DeleteColumn_D
Call AddTextToHeaders
Call AddGridlines

End Sub

Running all my Macros In excel instead of one by one

2 Answers2