help needed in modifying a macro to run in the other open workbook

[inolab]

I am completely new to creating macros/editing vba in Excel 2010, so please be patient with my question. I have written a macro, edited it and it works in any workbook I open when I run the macro from the Developer toolbar. I have saved the macro in its own excel file and have created a button to assign the macro to it. However, I am needing to share this macro so other users can open the xlsm file, click the button that has the macro assigned to it and then have the macro run on the other active workbook. We have over 140 different workbooks that we may or may not want to run the macro on, so I cannot specify the name of the workbook to run the macro on. Below is the beginning part of my macro (as it is pretty long), but I know I need to write something at the beginning that tells the macro to run on the other active workbook. I am at a loss as this is probably way above my skill level since this is my first macro to ever write. Any help is greatly appreciated


Sub diag_macro_test2()
'
' diag_macro_test2 Macro
'


'

Sheets("Sheet2").Select
Range("D1:H1").Select
Range(Selection, Selection.End(xlDown)).Select

 

[Joe4]

Welcome to the Board!

By default, if you do not specify a workbook name, it will run against the current active workbook in Excel. I would use that to your advantage and save it in your Personal Macro Workbook. Then, it will always be available to you whenever you have Excel open, and you can run it on any active file. You can even attach a keyboard shortcut to it (since you would not have a visible button to run it).

Just bear in mind that if you want others to have access to it also, you can have them create their own Personal Macro Workbook, and save it there as well.
See: https://support.office.com/en-us/ar...workbook-66c97ab3-11c2-44db-b021-ae005a9bc790

Some people will package these macros they want to share as an Add-In, and share them that way (another option).

 

[bobsan42]

in the posted part of your code there is nothing that points to one workbook or another.
So it would normally run and do its things on the active workbook.
However - if you are planning to use it on multiple files there is another option: save it as Excel Add-in (*.xla or *.xlam) and add (load) it to excel - in this way all functions and procedures from the file will be available no matter the workbooks.

 

[inolab]

It doesn't acknowledge the other open workbook. We have a shared drive that I intend to place this macro.xlsm file. I want other users to be able to open that macro file along with the workbook they are needing to balance and run the macro without them having to go to the developer tool. I will go look through the link you sent, but I have googled this issue for hours with not much luck finding a solution.

 

[inolab]

in the posted part of your code there is nothing that points to one workbook or another.
So it would normally run and do its things on the active workbook.
However - if you are planning to use it on multiple files there is another option: save it as Excel Add-in (*.xla or *.xlam) and add (load) it to excel - in this way all functions and procedures from the file will be available no matter the workbooks.

Joe4 also mentioned this "add-in" option. I will google how to do that as well and see how that works. Thank you.

 

[Joe4]

It doesn't acknowledge the other open workbook. We have a shared drive that I intend to place this macro.xlsm file. I want other users to be able to open that macro file along with the workbook they are needing to balance and run the macro without them having to go to the developer tool. I will go look through the link you sent, but I have googled this issue for hours with not much luck finding a solution.

If you have no specific workbook name reference in your code, then you will be able to run the macro against any active workbook you have open. However, that means that the macro call needs to be initiated from THE particular file you wish to run it against. So having a button on the workbook holding the macro wouldn't really work in that instance. That is why we made the recommendations that we did, as alternatives to do what you want.

However, if you wish to run it from a button on the file holding the macro, and the names will not always be the same, that still is possible, but with some caveats. Here are a few ways I can envision that happening.

1. Having it run against every open Excel workbook you have open, except the one calling the macro. We can loop through all open workbooks. Of course, the danger here is if they have an Excel file open that the don't want this to run against, it will hit that one too.

2. Have it loop though all open workbooks, but only run against ones meeting some sort of criteria. I have used this method myself. But you need some good way of identifying which workbooks to run against. While the file names are not always named the same thing, do the have some common methodology to their name (i.e. a specific prefix)? Or, do they always have a certain header? In other words, is their any way of looking at an open file and determining, yes, I want the macro to run against this workbook, or no, it should not run against it?

 

[inolab]

If you have no specific workbook name reference in your code, then you will be able to run the macro against any active workbook you have open. However, that means that the macro call needs to be initiated from THE particular file you wish to run it against. So having a button on the workbook holding the macro wouldn't really work in that instance. That is why we made the recommendations that we did, as alternatives to do what you want.

However, if you wish to run it from a button on the file holding the macro, and the names will not always be the same, that still is possible, but with some caveats. Here are a few ways I can envision that happening.

1. Having it run against every open Excel workbook you have open, except the one calling the macro. We can loop through all open workbooks. Of course, the danger here is if they have an Excel file open that the don't want this to run against, it will hit that one too.

2. Have it loop though all open workbooks, but only run against ones meeting some sort of criteria. I have used this method myself. But you need some good way of identifying which workbooks to run against. While the file names are not always named the same thing, do the have some common methodology to their name (i.e. a specific prefix)? Or, do they always have a certain header? In other words, is their any way of looking at an open file and determining, yes, I want the macro to run against this workbook, or no, it should not run against it?

Thank you Joe4. I am researching add ins now as well as how to distribute it to my network as read only. I think that was a great idea, but will take me time to learn how to do that. Code is not my thing, but am trying to force myself to learn and utilize it as an additional tool. the option you presented in above to have the macro run on all open excel workbooks might work as well. We usually only have 3 workbooks open max at one time while balancing and I can train staff to limit it to the workbook needing to be balanced and the macro.

Thanks again.

 

[Joe4]

You are welcome!

In case you decide to go that route, listed below is VBA code that will loop through all the open Workbooks, and run your code against every open workbook except the macro one that houses this code (assuming that is also the one you are calling the code from, such as through a button on one of the worksheets in the macro file):

    Dim wb As Workbook 
    Dim macroFile As Workbook
    
'   Capture current macro file
    Set macroFile = ActiveWorkbook

'   Loop through all open workbooks and compare name to macro workbook
    For Each wb In Workbooks
'       If not the macro workbook, activate the workbook and run the code
        If macroFile.Name <> wb.Name Then
            wb.Activate
'           run/call macro steps below

        End If
    Next wb

So, I would probably store all keep all your macro code in the current procedure, and just have a procedure call under the "run/call macro steps below" line, i.e.

Call MyMacro

 

[inolab]

You are welcome!

In case you decide to go that route, listed below is VBA code that will loop through all the open Workbooks, and run your code against every open workbook except the macro one that houses this code (assuming that is also the one you are calling the code from, such as through a button on one of the worksheets in the macro file):

    Dim wb As Workbook 
    Dim macroFile As Workbook
    
'   Capture current macro file
    Set macroFile = ActiveWorkbook

'   Loop through all open workbooks and compare name to macro workbook
    For Each wb In Workbooks
'       If not the macro workbook, activate the workbook and run the code
        If macroFile.Name <> wb.Name Then
            wb.Activate
'           run/call macro steps below

        End If
    Next wb

So, I would probably store all keep all your macro code in the current procedure, and just have a procedure call under the "run/call macro steps below" line, i.e.

Call MyMacro

WOW!!! This is awesome. Thank you so much. I will try it out in addition to the add-in and see which one is easier for everyone to utilize.