Calling a Macro from a Different WorkBook

[lac0403]

I have tried all the different versions I have seen on the boards, and still am getting a Compile Error: Expected End of Statement error.

Here is the setup
File 1: Macros.xlsm - which has macros in it to be run on other files
File 2: TG.xls - which I want to run the macros on. [yes, the old file is in compatability mode, I thought I would include this, as it may be relevant]

File1 Macro: Sub RunUpdates
-calls multiple sub routines for different updates to be stepped through.

File 2 Macro: UnhideCY (in Module modTGFinancials_Upd_2014, not a sheet module)
-is a macro within the target file, that unhides some of the sheets for updates. Because the CY sheets are renamed in each file, I wrote the macro using the sheet codename, and will put the macro into each file that needs the updates. (There are 240+files for updating.)

I would like to have in Sub RunUpdates a call out to UnhideCY, but have not been able to get any of the following to work:

Application.Run "'"&Application.ActiveWorkbook.Name&"'!modTGFinancials_Upd_2014.UnhideCY"
Application.Run "'"&Applicaton.ActiveWorkbook.Name&"'!UnhideCY"
Application.Run "'"&wbTG&"'!.UnhideCY" [where wbTG=activeworkbook, or the TG file]

I know I have not captured all the ways I have tried to make this work. And unfortunately, do not feel that I have explained well, or can explain better. (I have been working on this for 5 hours now, and think I am VBA drunk.)

Someone with fresh eyes who may see the error of my ways, would be greatly appreciated.

 

[K e v i n]

Hi,
The application run command takes the sysntax

application.Run "OtherWorkbookName.xls!Macro1"

(you only need the single quotes if the names have spaces)
so to run the macros in Macros.xlsm you would have

Sub RunUpdates
application.Run "Macros.xls!UnhideCY"
End Sub

I think that's what you are after but I'm not sure what you mean by 'File 2: TG.xls - which I want to run the macros on' - How the macros interact with other books would depend on their code.
Any way I hope this helps

 

[lac0403]

Thanks Kevin - I will give it a try. I was using the single quotes - as that was a suggestion on one the many sites & answers. I will rename files without spaces if I have to! Thanks again.