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

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.

(E-Mail Removed) wrote:
>
> 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

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

(E-Mail Removed) wrote:
> 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

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

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.

(E-Mail Removed) 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

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

--
Regards,
Tom Ogilvy


"John Coleman" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
> (E-Mail Removed) wrote:
>> 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
>

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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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
> >
> > (E-Mail Removed) wrote:
> >> 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
> >