Application.Run for every open workbooks (one by one)

[mxp44]

Hi guys,

One question:

Do you know how to convert the macro:

Sub UglyMacro ()
Application.Run
"FirstFile.xls!SameMacroButStoredInThisSpecificWorkbook"
ActiveWindow.ActivateNext
Application.Run
"SecondFile.xls!SameMacroButStoredInThisSpecificWorkbook"
ActiveWindow.ActivateNext
Application.Run
"ThirdFile.xls!SameMacroButStoredInThisSpecificWorkbook"
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

 

[Dave Peterson]

Notice your syntax:
"FirstFile.xls!SameMacroButStoredInThisSpecificWorkbook"

You have an exclamation mark after the workbook name and you use the workbook
name.

Application.Run "'" & wbk.name & "'!SameMacroButStoredInThisSpecificWorkbook"

The apostrophes are sometimes required--depending on the name of the file. But
they never hurt if you include them.

 

[John Coleman]

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

 

[mxp44]

Hi Dave,
Hi John,
Thanks a lot for your answers!

Just for feedback:
The code provided by Dave was almost perfect, I just needed to add
"wbk.Activate", as below:
-------------
For Each wbk In Application.Workbooks
wbk.Activate
Application.Run "'" & wbk.Name & "'!SameMacro"
Next i
------------

On the other hand, I was unable to run your code, John. Suppose I'm
missing something...

Cheers,
Mark

 

[Dave Peterson]

In general, you don't need to activate the workbook to run the macro.

But depending on what your code does and what workbook/worksheet should be
affected, you may even want to activate/select a specific sheet, too.

 

[Tom Ogilvy]

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

 

[John Coleman]

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