Thanks Tom
I see what happened. I wasn't sure about that point off the top of my
head so before my post I opened up Excel and wrote a quick sub to run
MsgBox ActiveWorkbook.Name and saw "Book 1" displayed - but I hadn't
*saved* it yet and the .xls is only added upon the first save.
Thanks again
-John Coleman

Tom Ogilvy wrote:
wb.name would include "xls", so you don't need to add that.

--
Regards,
Tom Ogilvy


"John Coleman" wrote in message
ups.com...
If the same macro is used in several workbooks maybe you can move it
into an add-in. Just an idea.

Maybe you can try

For each wb in Application.Workbooks
Application.Run wb.Name & ".xls!" & "SameMacro"
Next wb

I didn't test it but I hope it helps

-John Coleman

wrote:
Hi guys,

One question:

Do you know how to convert the macro:

Sub UglyMacro ()
Application.Run
"FirstFile.xls!SameMacroButStoredInThisSpecificWor kbook"
ActiveWindow.ActivateNext
Application.Run
"SecondFile.xls!SameMacroButStoredInThisSpecificWo rkbook"
ActiveWindow.ActivateNext
Application.Run
"ThirdFile.xls!SameMacroButStoredInThisSpecificWor kbook"
ActiveWindow.ActivateNext
End Sub

into macro that will go like:

Sub DraftNiceMacro ()
For each wbk in Application.Workbooks
Application.Run "wbk.SameMacroButStoredInThisSpecificWorkbook"
Next wbk
End Sub

This one is not working for some strange reason...

Thanks for your help,
Mark