Calling a Macro from another workbook

[Ricky Morris]

What's the syntax to call a macro in another workbook?

Thanks,
Ricardo

 

[Doug Mutzig]

Hi Rory,

I receive Run-time error '424': Object required and it highlights the code line:

Application.Run "'" & TrackingWorkbook.Name & "'!GlobalRemNamedRanges"

I think it might be with the macro itself? originally it had Option Explicit, but I removed that in testing and still get the same error. The code is:

Sub GlobalRemNamedRanges()     
    Dim nm As Name
     
    On Error Resume Next
        For Each nm In ActiveWorkbook.Names
            nm.Delete
        Next
    On Error GoTo 0
     
End Sub

Any ideas?

 

[RoryA]

That error means that you never assigned an object to the TrackingWorkbook variable.

 

[Doug Mutzig]

Ahhh ok, so I need to add a DIM line?

would this be a string?

 

[RoryA]

No, it would be a Workbook object and you need to assign the relevant workbook to it using Set.

 

[Doug Mutzig]

Ahh ok so then I have a follow-up question. when you say relevant workbook wouldn't I be entering in the specific workbook to go from? But my issue is that the origniating "relevant" workbook could be any of 20+ (MBU-TrackingWorkbook, SPD-TrackingWorkbook, etc.) Since those workbooks haven't been created yet, and I didn't want to have the admin overhead of setting each one up individually, how to I denote the relevant workbook? It would be the one currently open that has the name ****-TrackingWorkbook.xlsm (* as wildcard), but I am not sure how to do this. I am very new to vba and know just enough to be dangerous but not competent

 

[RoryA]

This should really be its own thread as it has nothing to do with the original one.

You need to loop through the workbooks and test the name of each one. As it happens, I've just answered an almost identical question here.

 

[Doug Mutzig]

Hi Rory,

Thank you very much for the information and the link!