Runtime Error 9 Subscript out of Range

[Jakson]

Help! This is the most ridiculously big brained problem I've ever run into. I'm not sure where or when the problem came about, but all of a sudden I can't reference one of my sheets anymore! The only way I can get VBA to run any code that has to do with range references or object references to this sheet is to use the indexed sheet value, in this case, sheet 3 of 15.

In previous subs, and in subs contained by other workbooks, I can use a variety of reference methods to get a worksheet or range in a worksheet. For some reason, no level of qualification or exactness, barring the literal command "sheet3", can activate or manipulate or even bring this mystery sheet into memory. I can see the sheet, it has not been modified by any user protections or hides or anything.

I'm completely miffed. Any ideas what brought this on? How to fix it? What to do to work around it?

I can attach some code if anyone is interested. I'd attach the workbook in question but its got HIPPA sensitive data on it and I'd need to do some heavy pruning.

 

[johnnyL]

Yep, post the subroutine that is giving the error and make sure you point out which line gives the error.

 

[Jakson]

Help! This is the most ridiculously big brained problem I've ever run into. I'm not sure where or when the problem came about, but all of a sudden I can't reference one of my sheets anymore! The only way I can get VBA to run any code that has to do with range references or object references to this sheet is to use the indexed sheet value, in this case, sheet 3 of 15.

In previous subs, and in subs contained by other workbooks, I can use a variety of reference methods to get a worksheet or range in a worksheet. For some reason, no level of qualification or exactness, barring the literal command "sheet3", can activate or manipulate or even bring this mystery sheet into memory. I can see the sheet, it has not been modified by any user protections or hides or anything.

I'm completely miffed. Any ideas what brought this on? How to fix it? What to do to work around it?

I can attach some code if anyone is interested. I'd attach the workbook in question but its got HIPPA sensitive data on it and I'd need to do some heavy pruning.

Yep, post the subroutine that is giving the error and make sure you point out which line gives the error.

Sub CommandButton1_Click()


Dim oRange As Range, aCell As Range, bCell As Range
    Dim ws As Worksheet
    Dim wsOut As Worksheet
    Dim ExitLoop As Boolean
    Dim SearchString As String, FoundAt As String
    Dim ****string As String
    Dim RNumIn As String
   
   
    'Back out any old data
    Workbooks("Billing.Name.B.09142021.v17.3.xlsm").Worksheets("Training Supervision").Range("B5:F20").Clear

Following code blocks are other methods I tried that also throw Runtime error 9

    'Back out any old data
    Sheets("Training Supervision").Range("B5:F20").Clear
...

    'Back out any old data
    Application.ActiveWorkbook.Worksheets("Training Supervision").Range("B5:F20").Clear

The only working method I've found to use is to select the sheet in question through the index value.

sheet3.value ...

 

[RoryA]

In that case, either the sheet name is wrong, or you're referring to the wrong workbook.

 

[johnnyL]

@Jakson can you post a screen shot of the Alt-F11 window?

 

[Peter_SSs]

Welcome to the MrExcel board!

Agreeing with Rory, but one thing to check is the name of the "Training Supervision" worksheet. Just check that it hasn't accidentally been changed, especially to include extra space characters leading/trailing/internal as that would not easily stand out but would cause such an error.

BTW, when trying to highlight a code line in the forum (eg Bold), use the RICH tags, not the VBA tags. I've fixed it for you above.

 

[Jakson]

In that case, either the sheet name is wrong, or you're referring to the wrong workbook.

Welcome to the MrExcel board!

Agreeing with Rory, but one thing to check is the name of the "Training Supervision" worksheet. Just check that it hasn't accidentally been changed, especially to include extra space characters leading/trailing/internal as that would not easily stand out but would cause such an error.

BTW, when trying to highlight a code line in the forum (eg Bold), use the RICH tags, not the VBA tags. I've fixed it for you above.

The very first thing I checked was that the sheet name, "Training Supervision" was spelled correctly. I re-named it to have proper case, and in doing so I would've deleted any trailing spaces behind or ahead. The sheet has never left the parent workbook. What I find especially bizarre is that I haven't had need to add any additional sheets until now. Only now is this being a problem. In several other routines I make references to [other] sheet names, all of which are hosted in the same parent book, by a variety of methods.

Is there anything else to sleuth for? Anything I might be overlooking besides the above? (Maybe I didn't look hard enough?)

 

[Jakson]

@Jakson can you post a screen shot of the Alt-F11 window?

I'm not fully understanding. Do you mean for me to pop open the VBA editor window and just snap a screenshot of that, or do you mean for me to enter into an error state and then screen shot, or some third thing? :''3

 

[johnnyL]

I'm not fully understanding. Do you mean for me to pop open the VBA editor window and just snap a screenshot of that, or do you mean for me to enter into an error state and then screen shot, or some third thing? :''3

Just as I said, a screen pic of the result that you get from a work sheet and you press Alt-F11

 

[Alex Blakenburg]

Just to cover off all basis. You said sheet3 works.
So with that workbook active.
Go to code window.
And in the immediate window copy paste the below and hit enter.
(if its not visible hit ctrl+G)
Then try your code again.

Sheet3.Name = "Training Supervision"