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.

 

[Jakson]

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

 

[Jakson]

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"

I'll give this snippet a try later today when I have a moment.

 

[Jakson]

I'll give this snippet a try later today when I have a moment.

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"

Alex, thank you very much. This resolved my issue.

Any idea what caused this? I've never experienced this issue until now.

 

[johnnyL]

I think you had a double space in the sheet name.

 

[Jakson]

I think you had a double space in the sheet name.

Besides user error, is there any other way for this to happen? I had re-typed the name of the sheet when I initially experienced the issue. I don't believe I put in a double space. However, I cannot say with certainty I did not.

What a funky typo, lmfao!

 

[johnnyL]

The code @Alex Blakenburg gave was just a rename of the sheet, so if that fixed your problem then there was a problem with the name of the sheet compared to what you were trying to call it.

 

[Jakson]

The code @Alex Blakenburg gave was just a rename of the sheet, so if that fixed your problem then there was a problem with the name of the sheet compared to what you were trying to call it.

Once the sheet rename code worked I figured it must've been some formatting issue. Reviewing the screen shot above, I think you're correct that I had added a double space between the first and second word. Boy I feel silly! LOL

 

[johnnyL]

It is often the little things that make you pull your hair out.

 

[Alex Blakenburg]

It is often the little things that make you pull your hair out.

Not to mention that once you have been staring at it for hours, you start to see what you expect to see, which is of course why you asked for a picture.

@Jakson the fact that you had tried it using the code name (Sheet3) and thought to mention it was also really helpful. I thought just renaming the sheet to what you had in the code would be quicker and save some back and forward correspondence (being in Australian and in a different timezone you become very conscious of the need to reduce the amount of toing and froing)

 

[Peter_SSs]

you start to see what you expect to see,

True, although ....

I would've deleted any ... spaces

.. sounded very much like an assumption, rather than a careful check of the following.

especially to include extra space characters leading/trailing/internal