Run Macro in another Workbook

[hensleyj]

Hey everyone,

I am having problems running a macro in another workbook. There are 2 workbooks, for the sake of simplicity, the "Main" workbook and "Customer" workbook. From the main workbook, the macro below is suppose to make the "customer" workbook visible, then activates it and runs the macro.

I have tried a variety of ways to make this work, and each time it fails to run the macro with warnings that it cant find it because its moved or name is wrong. Or the audbscript is out of range.

The "customer" workbook will change as each user has their own. So the name of it needs to be dynamic to ensure it runs the macro from the correct workbook.

I have a number of macros that need to be run in the same fashion, but i cant get this to work. anything you can do to help would be amazing!

Here is some info that may help you help me where the dynamic references are:

Dynamic range references located on Worksheet "Ranges"
file path: C375
workbook name: C374

sub Macro1()
 Application.ScreenUpdating = False
Windows(Sheets("Ranges").Range("C374").Value & ".xlsm").Visible = True
Workbooks(Sheets("Ranges").Range("C374").Value & ".xlsm").Activate
Application.Run ("""'" & Sheets("Ranges").Range("C374").Value & ".xlsm" & "'!Border""")
end sub

 

[JackDanIce]

Try:

Sub Macro1_v2()
    
    Dim wkb As Workbook
    
    Set wkb = ThisWorkbook.Sheets("Ranges").Range("C374").Value
    
    With wkb
        .Activate
        Application.Run .Name & "!Border"
    End With
    
    Set wkb = Nothing
    
End Sub

 

[hensleyj]

Hey mate,

This produces the same error i also received "Runtime error '424': Object required"

It debugs and highlights this line as the issue;

"Set wkb = ThisWorkbook.Sheets("Ranges").Range("C374").Value"

Any ideas?

 

[JackDanIce]

That error is suggesting there isn't a workbook with the provided name open in the same session as your main workbook.

What value is in that cell? Can you post the entire cell contents of Sheets("Range"), Range("C374")?

 

[hensleyj]

the name of the workbook, i am trying to run the Macro on is named: Jason Hensley.xlms
Cell C374 = Jason Hensley

I also tried CELL C376 which had: Jason Hensley.xlms

But neither worked.

 

[JackDanIce]

Is that workbook open before you try to run the macro? Your code suggests it should already be open.

 

[hensleyj]

yep, its already open though invisible.

I have a line which makes it visible again, then the macro should run. Everything works until it tries to get the macro to run

 

[JackDanIce]

Try keeping it visible but not the active workbook when you run the code.

Also, have you tried recording a macro and comparing to the resulting code? That may suggest where the difference is...

 

[hensleyj]

Already tried it a bazillion times in different ways

Sheet was open and visible but not the active sheet, and it still has same error.

 

[JackDanIce]

And you've not been able to record a macro to copy your actions? It seems to be there's a naming or other syntax error and what you have in the cell doesn't match the workbook name, else that Set line would not error. Without your workbooks or macro names, difficult to guess what the issues is as I can't recreate it here.