Run Macro to all open workbooks.

Hello!, I am new to excel and macro recording is a big help for me.
I have 100 excel workbooks i one folder(This can't be consolidated due to management reasons), my problem is every time we are making changes in in templates or formulas,what i am currently doing is I will record a macro and run each in every 100 workbooks, is there any way that i can run / click to activate macro in all open workbooks? by the way all my workbooks have same template.
Macro recording and run it to each workbooks is very helpful to me, but i'm worried that soon my 100 workbooks will doubled.

You could do it with something on the lines of

Please Login or Register  to view this content.

Note that you would need to qualify any object / sheet references in your existing code with a wb. prefix.

Thank you so much, i created a new workbook and a button to run this code i just tried to add date in all open workbooks, and last question, does the recorded macro code will work here too? i didn't try coz i don't wanna ruin my template

Sorry for my grammar hope u understand.

It should either be wb.Worksheets or wb.Sheets, both in the same line will cause an error. Selection, ActiveCell or ActiveSheet should not have anything in front.

Please Login or Register  to view this content.

In addition to that, the macro recorder does not produce very efficient code, selection and activation are almost never needed, everything in red above can be deleted and the code will still function.

Please Login or Register  to view this content.

Enhancing that further still, if you have a lot of lines that will refer to wb.WorkSheets(1) then you can use a 'With' block to cut down on the typing / copy & paste needed to write the code.
Note that the lines between With and End With all start with a dot / period, this links them to wb.WorkSheets(1) that the section is working 'With', if there is no dot / period at the start of the line then that line might not be linked to the correct sheet.

Please Login or Register  to view this content.

One final point. If you have multiple instances of Excel open, then this will not span all of them. Only the workbooks in the instance that holds the code.

Wow! This code did exactly what i wanted to, Thank you so much for your help.

You're welcome! Thanks for the feedback and rep!

Just a quick check, do you understand the bit about multiple instances of excel?

On older versions, opening workbooks from windows folders would cause each to open in a new instance, opening them from the file menu would open them all in the same instance.
This was changed at some point, so I'm not sure if that is how your version would work or not.

Actually all i know in excel as of now is the mostly use formulas and formats, and i was introduce in VBA a week ago since i need some codes to lessen my working hours, i have a guess what multiple instances is, and in some cases i experience that when 1 excel file is generating and unable to use the other files are affected, is this what multiple instances means? I am using Excel 2016 by the way.

As you're using 2016, you should be safe. Microsoft made opening a second instance of excel so complicated that it's almost impossible to do it even when you want to.

If you are unable to do anything with other excel files while one is working then they are all in the same instance.

If you are able to make changes to one while another is working then they are in separate instances.

As long as you only open excel once, either from the windows desktop / start menu, or by double clicking a workbook icon in a folder you can guarantee that all will be in the same instance regardless of which version you are using.
Once you have done this, open the rest of your workbooks from within excel by going to the File tab on the ribbon, then Open.