Stack Three Macros into One VBA Command

Question

I have three macros (below) that work separately, but when I sandwich them together, only the first macro executes properly. I'm not getting an error; the other two macros just don't seem to run. Any advice on how to link them together so I can execute all at once?

Macro 1

Sub Update_Workbook()
Dim QryStr As String, cell As String
Dim a As Integer, b As Integer
Dim cellv As Variant

'Pause spreadsheet calculations until end of sub
Application.Calculation = xlManual

ActiveWorkbook.Sheets("Raw Data").Select

'Clear cells to import query
With Range("A1:O1").EntireColumn
    .ClearContents
    .NumberFormat = "General"
    .Validation.Delete
End With

'Process SQL query string
QryStr = ActiveSheet.TextBox1.Value
Do While InStr(QryStr, "{&")
    a = InStr(QryStr, "{&")
    b = InStr(a, QryStr, "}")
    cell = Mid(QryStr, a + 2, b - a - 2)
    cellv = Range(cell).Value
    If IsDate(cellv) Then
        cellv = Format(cellv, "dd-mmm-yy")
    End If
    QryStr = Replace(QryStr, "{&" & cell & "}", cellv)
Loop

'Import data from query
    With ActiveSheet.QueryTables.Add(Connection:="ODBC;DRIVER={Oracle in OraClient11g_home1};UID=xx;PWD=xx;SERVER=xx;DBQ=xx", _
  Destination:=Range("A1"), Sql:=QryStr)
    .MaintainConnection = False
    .BackgroundQuery = False
    .RefreshStyle = xlOverwriteCells
    .Refresh
    .Delete
End With
Finish_Sub:
Call ClearUnneededNames
Application.Calculation = xlCalculationAutomatic
End Sub
Sub ClearUnneededNames()
Dim savedNames As Integer
savedNames = 0
Do While ActiveSheet.Names.Count > savedNames
    If InStr(ActiveSheet.Names(savedNames + 1).Name, "ExternalData") = 0     Then
        savedNames = savedNames + 1
    Else
        ActiveSheet.Names(savedNames + 1).Delete
    End If
Loop
End Sub

Macro 2

Sub Five_Felicia_for_MFG()
'
' Macro3 Macro
'

'
Range("A3:M3").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlUp)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWindow.SmallScroll Down:=-18
Range(Selection, Selection.End(xlUp)).Select
Range("A3:M1010").Select
Selection.Delete Shift:=xlUp
Sheets("5Felicia").Select
Range("A3:M34").Select
Selection.Copy
Sheets("5Felicia for MFG").Select
Range("A3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
Sheets("5Felicia").Select
Range("A37:M37").Select
Range(Selection, Selection.End(xlDown)).Select
Range("A37:M692").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("5Felicia for MFG").Select
ActiveWindow.SmallScroll Down:=18
Range("A36").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=-48
Columns("A:M").Select
Application.CutCopyMode = False
ActiveSheet.Range("$A$1:$M$691").RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6, _
    7, 8, 9, 10, 11, 12, 13), Header:=xlNo
End Sub

Macro 3

Sub DUMMY_ITEMS()
'
' DUMMY_ITEMS Macro
Dim LastRow As Long

Sheets("Operations").Range("H2:V73").Copy

With Sheets("Raw Data")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("A" & LastRow + 1).PasteSpecial xlPasteValues
End With

End Sub

You show no effort to *link them together* (whatever that means). Do you mean *execute in sequence*? Create a new macro that calls the three existing ones one after the other. If you've got code you haven't shown us that tries to do so that doesn't call them, use the debugger to step through the code to find out why. — Ken White, Jan 27 '17 at 01:07
Macro 1 appears to have a copy-paste error (or a syntax error). — Comintern, Jan 27 '17 at 01:12
Put a breakpoint and run and see what happens. Neither 'sandwich together' or 'doesn't seem to run' gives us any clue to start troubleshooting — Nick.Mc, Jan 27 '17 at 01:14
The syntax error in Macro 1 may have popped up when I removed database credentials so I could post here. @Comintern — ERKSMTY, Jan 27 '17 at 01:15
I'm not even sure what a breakpoint is. Super new to this. I think my issue is that I don't know how to sequence separate macros together. @Nick.McDermaid — ERKSMTY, Jan 27 '17 at 01:16
In the VBA editor, if you hit `F9` on a line, it will create a breakpoint. This is a point when reached, the code will stop and focus your attention to the editor window. Also, it's **highly** suggested to [avoid using `.Select`/`.Activate](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) as it can cause error or unexpected behavior. — BruceWayne, Jan 27 '17 at 01:20
How would I call the three existing macros one after the other? Is this what the breakpoint is used for? @KenWhite — ERKSMTY, Jan 27 '17 at 01:21
A breakpoint allows you to step through the code line by line in the debugger so you can follow the execution path, examine variable values, etc. You would call the three existing macros by doing that; call the first macro you want to execute, then the second macro you want to execute, and then the last macro you want to execute. — Ken White, Jan 27 '17 at 01:27

score 1 · Accepted Answer · answered Jan 27 '17 at 13:07

In VBA a Module can have several macros (or, more accurately, subroutines) in it.

But, when you call one of those macros, execution stops at the End Sub statement. Nothing else in the module will run regardless of how you "sandwich" them together within the module.

But subroutines can call other subroutines. So code like this will run all three of your macros:

Sub RunAllThree()
    Update_Workbook
    Five_Felicia_for_MFG
    DUMMY_ITEMS
End Sub

Stack Three Macros into One VBA Command

1 Answers1