Run Time 1004 error when calling sub from another macro

[Curben]

I am tryingto run a macro existing in another workbook opened by a variable, I am using the following code to try and get it to work but i get Run-time error '1004':

Cannot run the macro "Test 5.xlsm'!UpdTables'. The macro may not be available or all macros may be disabled.

Application.Run "'" & Application.ActiveWorkbook.Name & "'!UpdTables"

 

[RoryA]

What actually happens?

 

[ZVI]

Code in workbook A: Run "'B1.xls'!UpdTables"
Change "B1.xls" to the actual name of workbook B1

Put this code in Module1 of workbook B1 for testing:

Sub UpdTables()
   MsgBox "'" & ThisWorkbook.Name & "'!UpdTables"
End Sub

Workbook B1 should be open.

 

[mp4-4]

What actually happens?

Error 1004: Application-defined or Object-defined error

Code in workbook A: Run "'B1.xls'!UpdTables"
Change "B1.xls" to the actual name of workbook B1

Put this code in Module1 of workbook B1 for testing:

Sub UpdTables()
   MsgBox "'" & ThisWorkbook.Name & "'!UpdTables"
End Sub

Workbook B1 should be open.

ZVI, I replaced ThisWorkbook.Name with the actual name of the workbook, same error 1004 occurs.

The MsgBox does not display: this means that the problem is somewhere with my Workbook A and its way of calling B2's macro

 

[ZVI]

ZVI, I replaced ThisWorkbook.Name with the actual name of the workbook, same error 1004 occurs.
The MsgBox does not display: this means that the problem is somewhere with my Workbook A and its way of calling B2's macro

Please post the code of workbooks A and B1.
And what is actual name of B1 workbook?

 

[mp4-4]

Please post the code of workbooks A and B1.
And what is actual name of B1 workbook?

Inside 'A':

Private Sub CommandButton2_Click()
            
     Application.ScreenUpdating = False
     
        Worksheets("Input1").Unprotect
     
         ' Copy from A to B Macro
         '
        Dim wbTarget As Workbook 'workbook where the data is pasted to: B.xls
        Dim wbThis As Workbook 'workbook from where the data is copied from: A.xls
         
        Set wbThis = ActiveWorkbook  'set to the current active workbook (the source book)

        'open a workbook that has same name as the sheet name
        Set wbTarget = Workbooks.Open(ThisWorkbook.Path & "\" & "B van_64x.xls")

        wbThis.Worksheets("Link1").Range("A1:Z30").Copy  'copy the range from source book
        wbTarget.Worksheets("Link1").Range("A1").PasteSpecial xlPasteValues  'paste the data on the target book
        Application.CutCopyMode = False  'clear any thing on clipboard to maximize available memory
        wbTarget.Activate

        'Call Reset Macro
        Application.Run wbTarget.Name & "!Module1.ResetTables"

        wbTarget.Close savechanges:=True  'close the workbook

    Application.ScreenUpdating = True
    
End Sub

Inside 'B':

Sub ResetTables()
'
' Keyboard Shortcut: Ctrl+Shift+R

    Range("I26").Select
    ActiveCell.FormulaR1C1 = "=LinkB!R[-18]C[-7]"
    Range("I26").Select
    Selection.AutoFill Destination:=Range("I26:I27"), Type:=xlFillDefault
    Range("I26:I27").Select
    Range("I29").Select
    ActiveCell.FormulaR1C1 = "=LinkB!R[-16]C[-7]"
    Range("I33").Select
    ActiveCell.FormulaR1C1 = "=LinkB!R[-16]C[-7]"
    Range("I33").Select
    Selection.AutoFill Destination:=Range("I33:I35"), Type:=xlFillDefault
    Range("I33:I35").Select
    Range("I37").Select
    ActiveCell.FormulaR1C1 = "=LinkB!R[-30]C[-1]"
    Range("I37").Select
    ActiveCell.FormulaR1C1 = "=LEFT(LinkB!R[-30]C[-1], 1)"
    Range("N18:O18").Select
    Selection.AutoFill Destination:=Range("N17:O18"), Type:=xlFillDefault
    Range("N17:O18").Select
    Range("O17").Activate
    Selection.Font.ColorIndex = 54
    Range("N26:O26").Select
    Selection.AutoFill Destination:=Range("N25:O26"), Type:=xlFillDefault
    Range("N25:O26").Select
    Range("O25").Activate
    Selection.Font.ColorIndex = 54
    Range("M23").Select
    ActiveCell.FormulaR1C1 = "=LEFT(LinkB!R[-11]C[-9], 1)"
    Range("M21").Select
    ActiveCell.FormulaR1C1 = "=LinkB!R[-14]C[-9]"
    
    
End Sub

 

[ZVI]

1. Delete/comment the code line with Application.Run
2. Copy this code and paste it to the CommandButton2_Click subroutine instead of the deleted one:

        'Call Reset Macro
        Application.Run "'" & wbTarget.Name & "'!Module1.ResetTables"

It is expected that ResetTables subroutine is in wbTarget workbook

3. The code of ResetTables may be optimized like this:

Sub ResetTables()
'
' Keyboard Shortcut: Ctrl+Shift+R
 
    Range("I26").FormulaR1C1 = "=LinkB!R[-18]C[-7]"
    Range("I26").AutoFill Destination:=Range("I26:I27"), Type:=xlFillDefault
    Range("I29").FormulaR1C1 = "=LinkB!R[-16]C[-7]"
    Range("I33").FormulaR1C1 = "=LinkB!R[-16]C[-7]"
    Range("I33").AutoFill Destination:=Range("I33:I35"), Type:=xlFillDefault
    Range("I37").FormulaR1C1 = "=LinkB!R[-30]C[-1]"
    Range("I37").FormulaR1C1 = "=LEFT(LinkB!R[-30]C[-1], 1)"
    Range("N18:O18").AutoFill Destination:=Range("N17:O18"), Type:=xlFillDefault
    Range("N17:O18").Font.ColorIndex = 54
    Range("N26:O26").AutoFill Destination:=Range("N25:O26"), Type:=xlFillDefault
    Range("N25:O26").Font.ColorIndex = 54
    Range("M23").FormulaR1C1 = "=LEFT(LinkB!R[-11]C[-9], 1)"
    Range("M21").FormulaR1C1 = "=LinkB!R[-14]C[-9]"
   
   
End Sub

 

[RoryA]

So you're not getting a "can't find macro" type error?

It sounds like the resettables routine might be failing. If you add this line to the start of it

msgbox "called"

do you see the message when you click the button in A?

 

[ZVI]

So you're not getting a "can't find macro" type error?

It sounds like the resettables routine might be failing. If you add this line to the start of it

msgbox "called"

do you see the message when you click the button in A?

Hi RoryA,
Have sense!
But also space char is in the name of wbTarget, thus workbook's name should be surrounded by single quotes in Run expression.
Vlad

 

[RoryA]

Hi Vlad,

You're right of course - I missed the file name in the code.

 

[mp4-4]

Hi RoryA,
Have sense!
But also space char is in the name of wbTarget, thus workbook's name should be surrounded by single quotes in Run expression.
Vlad

Thank you Vlad,

Application.Run "'" & wbTarget.Name & "'!Module1.ResetTables"

works as intended. What I don't understand is why this style works in another Workbook but not in this one?

Rory, thank you too.