Why do i get run-time error 9: subscript out of range error?

So I've macros that work perfectly when I copy and paste it into the developer tool. But instead of doing that to each new excel sheet I open, I want to have it open in the background in the setting Macros in 'All Open Workbooks'. But I get the titled error instead. Here are the macros:

Macro 1:

Please Login or Register  to view this content.

Macro 2:

Please Login or Register  to view this content.

Thanks!

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however you need to include code tags around your code.

Please take a moment to add the tags. Posting code between [code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code] tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

I have done this for you to keep things moving along.

When you are asking about a VBA error always indicate which line of code causes the error.

Every error is line related. It is not possible to help you diagnose this error without knowing what line of code caused it. This error can be caused by multiple problems and I can't go through your code line-by-line to try to guess what it is.

This error can be caused by using Worksheets() or Sheets() referring to a worksheet name that does not exist. It is possible that is what is happening in your code. But without more information it's just a guess.

when another workbook tries to use it

I don't understand what you mean by this. Do you mean that code in another workbook that calls the code in this one? Or did you copy this code to a different workbook?

This is the last guess I am going to make without knowing which line of code causes the error. The workbook containing this code must have a worksheet named "Routing". Is that true when this error occurs?

ThisWorkbook refers to the workbook with the code in it. Perhaps you should try using ActiveWorkbook instead.

Originally Posted by skhande2

when I execute it on a different workbook like this:
ibb.co/ DrDpTnm- remove space to see the picture.

You can upload images directly to a post, which saves steps over uploading to a third-party site then including a non-working link to it.

runmacro.jpg

Originally Posted by skhande2

To use active workbook, I would need to copy paste the macro to the new workbook each time.

No, you would not. It will refer to whatever workbook is active when the code is running, no matter which workbook the code is in. ThisWorkbook by contrast, as Trevor said, always refers to the workbook with the code in it.

There are three ways to refer to a workbook:

1) As ThisWorkbook. As already noted, this ALWAYS refers to the workbook that contains the code. It sounds like you want the code stored in a common workbook, so that the code will never really work on the workbook that contains the code.
2) As ActiveWorkbook. This ALWAYS refers to the currently active workbook. This does not mean that the code must be housed in the active workbook; only that the workbook must be active at the time of execution. You can use ActiveWorkbook as long as you can be confident that you and your users will have the desired workbook open when executing the code.
3) As a named member of the Workbooks() collection. When you do not want to ALWAYS work on ThisWorkbook and cannot be certain that ActiveWorkbook will ALWAYS be the correct workbook, then you will need to use the Workbooks() collection to access the desired workbook. If you know the name of the workbook will always be the same, you can hardcode the name into the procedure. Or you can use an inputbox to have the user enter the name, or any of a number of other strategies for getting the desired workbook name so your procedure can access the desired workbook.

Currently, your code is using ThisWorkbook, which seems like the wrong approach for what you want.

I think many programmers would design this procedure to work on ActiveWorkbook with an error trap that traps the "wrong workbook open and active at time of execution" error with a message that the user should be sure that the correct workbook is open and active before executing the procedure and then terminating the procedure. This seems like it could easily be made to work for a "when I open a new workbook [so it should be the ActiveWorkbook], I can just execute [this procedure being stored in another workbook] with a shortcut" kind of scenario.

If you decide that you don't want to use either ThisWorkbook or ActiveWorkbook, then you as the programmer need to figure out how you would like the procedure to know what workbook it should be working on. Then add the appropriate lines of code to the procedure.

How would you like to proceed?

You can also refer to workbooks using the Workbooks Collection Index. Maybe not appropriate here but you can loop through the collection checking the workbook names.

I think the consensus is to refer to the Active Workbook, that is, the workbook that is selected and which you are working on, or wish to operate on. ThisWorkbook would be the "library" for the code and sitting in the background. This would need to be opened first so that the code is available for other workbooks.

Might have missed something but I don't think we've seen an example workbook where this works and one where it doesn't. Difficult to come up with constructive suggestions without the context.


Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file and a mocked up solution.

You cannot "save a macro as all open workbooks". A macro exists in one workbook (but can affect many). As written, your code will only ever work on the workbook containing the code because it refers to ThisWorkbook, not ActiveWorkbook.