Running a macro in another workbook

B

Blue

I have a workbook called "blank" that has a macro in it called "report". I
want to call the "report" macro from several other workbooks and copy the
results back to the workbook/sheet that asked the "report" macro to run.

Thanks for your help

Blue
 
R

Ron de Bruin

Use Application.run to call a macro in a other open workbook
Some notes on this from Chip Pearson

Application.Run "Book1.xls!MacroName"

Or, if the book name contains spaces, (watch the quotes)

Application.Run "'Book One.xls'!MacroName"
 
B

Blue

Ron

Thanks for the reply, the workbook with the macro in will be closed, will
this still work?

Blue
 
R

Ron de Bruin

Hi Blue
No

If you want to use the macro in all your workbooks you can copy the macro in
your personal.xls.
This is a (normal) hidden workbook that is loaded automatically by Excel.
When you record a macro, you have the option of recording it to your
Personal Macro Workbook.
The file, Personal.xls, is stored in your \XLStart directory.

The easiest is to record a dummy macro and choose Personal Macro Workbook.
Excel create the file for you this way.
Then copy your macro in this file and delete the dummy macro.
 
B

Blue

Ron

I need to make things a bit clearer.

The "blank" workbook with the "report" macro is on a network. The "report"
macro will be called by other workbooks also on the network. The "blank"
workbook will be closed. The other workbook will opened by different people
on different days, no personal.xls will be used


Example
Open other workbook - Need data provided by "report" macro.
Ask report macro to get the data - this will be copied into a tab in
"blank" workbook.
Copy "report" data from "blank" to other workbook.

How do I get "blank" open run "report" and copy data back to the workbook
that requested the data.

There are reasons why the "report" macro cannot be in the same workbook that
is requesting the information.

Thanks

Blue
 
Top