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

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

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

Notice your syntax:
"FirstFile.xls!SameMacroButStoredInThisSpecificWor kbook"

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.

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

--

Dave Peterson

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

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

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

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

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

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.

wrote:

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

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

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

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

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