Fire macros from a single macro....

[pan20]

Hey guys ,
i have 4 macros that copy paste data from another workbook.
I want to have one macro that fires the other 3. Note that each macro is placed into a different sheet, in the same workbook. Is this possible?
How can we call the other 3 macros if they are in a different sheet?


Thanks

[westconn1]

the macros need to be public procedure, at least not private

sheet1.mymacro

[pan20]

the macros need to be public procedure, at least not private

sheet1.mymacro

what if the macros aree in different workbooks in the same folder.
can't we do it?

[westconn1]

yes you can do

Set s = Workbooks("book1.xls").Sheets("sheet1")
Application.Run s.mymacro
' or for macros in a module
application.run "book1.xls!mymacro"

[pan20]

yes you can do

Set s = Workbooks("book1.xls").Sheets("sheet1")
Application.Run s.mymacro
' or for macros in a module
application.run "book1.xls!mymacro"

i tried to put the code to my main workbook to fire the other macros my i get an error.
Subscript out of range..
Set s = Workbooks("All brand Projects.xls").Sheets("sheet1")
Application.Run s.mymacro
' or for macros in a module
application.run "All brand Projects.xls!mymacro"

don't we need to open the other workbooks before we fire the macros?
i placed that code to a module on my main spreadsheet(master data). and i need to link it with the other workbooks so i can run the macros...
i tried it but am getting errors

[westconn1]

i did assume they would be open already

Set s = Workbooks.Open("book1.xls").Sheets("mysheet")
Application.Run s.mymacro

if you want a workbook object set that first on open, then set the sheet

[pan20]

i did assume they would be open already

Set s = Workbooks.Open("book1.xls").Sheets("mysheet")
Application.Run s.mymacro

if you want a workbook object set that first on open, then set the sheet

Set s = Workbooks.Open("\\NASFILE02\prou\My O Files\Desktop\status 2\FINA\fiNAL\Publish on\Express.xls").Sheets(" Express Report")
Application.Run s.Sample4thats my macro.
am getting this error.
object doesn't support this property or method.
any idea on what am doing wrong?

[westconn1]

can't see from here, the code should work ok, i have tested on a couple of workbooks, you sure the macro name is correct and on the correct sheet
which line do you get error?

[pan20]

QUOTE=westconn1;3765403]can't see from here, the code should work ok, i have tested on a couple of workbooks, you sure the macro name is correct and on the correct sheet
which line do you get error?[/QUOTE]

am getting error in this line................
Application.Run s.Sample4

after s. i just put the name of my macro that is included in a module.....
is there any way we can run a macro without opening the workbooks?
updating the workbooks without opening them?

[westconn1]

is there any way we can run a macro without opening the workbooks?
updating the workbooks without opening them?

no, you have to open each make changes and close & save, no need to be visible to the user