Running vba in a different workbook, then continuing original code.

[Godders199]

Hello, I have the following code which selects data from the summary tab, copies it, opens up a template workbook, then runs Marco3 in that book.

The last action in Marco3 is to close the template workbook

Once that completes I want to call a marco in the original summary workbook, but I cant get the code to continue after marco 3 completes. The code below I have put in an if and end if statement, but that does not help.

Msgbox “it works” is purely there to show me something happens after marco 3 is complete and the template workbook shut

I cant get this msgbox to appear.

If I f8 through the code it just stops on End if.

Can someone tell me what I need to change to effectively add more code where the msgbox is so it will run.

I have removed the full file addresses for this question incase anyone things that is the problem.


Application.CutCopyMode = False

Application.ScreenUpdating = False

' auto Macro

'Dim XM as long

Sheets("summary").Select

If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilter.ShowAllData

Cells.Select

ActiveSheet.Range("A:af").AutoFilter field:=26, Criteria1:="<>"

If Application.CountIf([Ab:Ab], "<>") > 1 Then

Range("y1:aa1").Select

Range(Selection, Selection.End(xlDown)).Select

Selection.SpecialCells(xlCellTypeVisible).Select

Selection.Copy

ChDir _

"S:\BAL Templates"

Workbooks.Open Filename:= _

"S:\FileCheck Template.xls"

Range("A2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Rows("2:2").Select

Application.CutCopyMode = False

Selection.Delete Shift:=xlUp

Range("A2").Select

Application.Run "'S:Macro3"

End If

MsgBox "it works"

Application.ScreenUpdating = True

Application.CutCopyMode = True

 

[rlv01]

It is very difficult to understand your code when you do not use the code tags ("<vba/>" in the menu bar) to wrap your code when you post a code example. A lot of people skip over the posters that do this because it is too much trouble to untangle.

Application.Run "'S:Macro3"

...Once that completes I want to call a marco in the original summary workbook, but I cant get the code to continue after marco 3 completes.

Passing execution to a macro in another workbook can be tricky. Whether or not the macro execution returns from Macro3 depends on what Macro3 is doing and how it is coded. Here is a simple example:

'This macro to be located in a code module for an already open workbook named "Book1"
Sub Book1Macro()
    ThisWorkbook.Activate
    MsgBox "Book1 macro *before* calling  a macro in Book2", , "Active Workbook: " & ActiveWorkbook.Name

    Application.Run "Book2!Book2Macro"

    ThisWorkbook.Activate
    MsgBox "Book1 macro resuming execution *after* calling a macro in Book2", , "Active Workbook: " & ActiveWorkbook.Name
End Sub

'This macro to be located in a code module for an already open workbook named "Book2"
Sub Book2Macro()
    Dim Ans As Integer
    ThisWorkbook.Activate
    Select Case MsgBox("Book2 macro is executing now," & vbCrLf & vbCrLf _
                     & "Ok - Continue" & vbCrLf & vbCrLf _
                     & "Cancel - Abort", vbOKCancel Or vbQuestion, ActiveWorkbook.Name)

    Case vbOK
        Exit Sub
    Case vbCancel
        MsgBox "Abort. No return to Book1 macro", , "Active Workbook: " & ActiveWorkbook.Name
        End
    End Select
End Sub

Sub Book2Macro is coded in such a way that pressing "Ok" will allow the macro execution to return to the Book1 macro, but pressing "Cancel" will prevent it. You must insure that the code in your Macro3 will not prevent a return to the calling macro.