Apply Macro to Multiple Workbooks...

  • I have 20 workbooks that need one macro applied to them. I've already written the macro, which resides in a standard module. How do I modify the macro that I've written to apply to the other workbooks?


    This is probably a simple request, but I can't seem to find the answers I need in the help files.


    Thanks for any assistance.


    Bubbis

    :silverha:

  • Re: Apply Macro to Multiple Workbooks...


    Quote from Bubbis Thedog

    How do I modify the macro that I've written to apply to the other workbooks?


    just a suggestion,why not save the macro into an add-in?
    that way you can use it on other workbooks

    Cheers
    ___________
    Xlite :sheep:
    [st]All you need to learn VBA is an internet connection and Ozgrid.com[/st]

  • Re: Apply Macro to Multiple Workbooks...


    Thanks for the suggestion, xlite. I saved the workbook containing the macro as an .xla file in the default AddIns location. So, now I have the option to checkmark it in the Add Ins dialog box.


    Where I'm a tad confused right now is what to do next. When I checkmark the box next to my Add In, I've noticed that the Add In project appears in the VBE for every workbook that I open thereafter. If I uncheck the box, and then close and reopen Excel, the project is gone. So I know how to activate it, and deactivate it. But a couple of Qs:


    1) In order to run the Add In, is it necessary for me to run it from the VBE (by pressing the "Play" button, for instance), or is there a way to run it from the toolbar?


    2) How do I add a description to the Add In? The Properties for the file will does not have a section to do so.


    Thanks for your assistance. I learn a great deal from all of you every day.


    Bubbis

    :silverha:

  • Re: Apply Macro to Multiple Workbooks...


    Hiya
    If all the workbooks on the same computer or network you could use the Personal.xls it will store the macro in on the Excel as a whole rather than just the one work book. so you can run the same macro for any and every .xls you want.

  • Re: Apply Macro to Multiple Workbooks...


    Thanks for the reply, 4508. I think, instead of the Add-In, I'll for now go with creating a custom button on the toolbar, and assign my macro to it. This makes the process drastically quicker --though I still must open every workbook.


    Thanks again,


    Bubbis

    :silverha:

  • Re: Apply Macro to Multiple Workbooks...


    Bubbis


    Are all the worksheets in the same direcotory or are are named consistently by some convention?


    If so you could use the FileSearch object to return all the workbook names.


    Then open each workbook like this:


    Set wb = Workbooks.Open ("C:\MyWorkbook.xls")


    You can then use the reference wb in your macro.

    Boo!:yikes:

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!