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




"Ron de Bruin" wrote in message
...
Hi Blue

will this still work?
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.





--
Regards Ron de Bruin
http://www.rondebruin.nl


"Blue" wrote in message
. ..
Ron

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

Blue
"Ron de Bruin" wrote in message
...
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"


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Blue" wrote in message
.. .
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

Reading the thread of notes is somewhat confusing. But, if I understan
the problem correctly, the real issue is keeping track of whic
workbook has the data, which workbook has the macro, and which workboo
you want to copy the data to.

My suggestion would be to use a temporary worksheet to stuff thi
information into.

You certainly will always know the name of the workbook that has th
macro in it, since it is the one on the network that everyone else wil
be calling. So, that is the workbook in which I would put th
temporary worksheet.

When any other workbook makes a call on the macro-containing workbook
record its specifics (workbook.name, sheet.count, or whatever else yo
might need) in certain cells of the temporary worksheet.

There is a slicker way of doing all of this that uses virtual memor
and relies on a "trick" based on what Excel does to still allow it t
process Excel4 (i.e., ancient) macros. But, that is so slick that i
is easy to confuse yourself ... which is why I stick with usin
temporary worksheets instead of virtual memory to pass information pas
and forth between and among various macro modules

--
Message posted from http://www.ExcelForum.com