Hi,



I simply want to activate a worksheet in a file I already have open.
The sheet_name has a BLANK, however, so just putting the variable_name holding the sheet_name in brackets causes an error.
What to do here?

Thanks a lot!

Best regards,

DataNibbler

What error are you getting ?

Hi,

I am getting a Runtime_error 9 "Subscript out of Range". I don't know for sure what is the reason, but that line is highlighted, so I guess it's because of the BLANK in the name.
I resorted to accessing the sheet by number, that works, but it's not very secure - anyone might add a sheet for whatever reason so the numbering would change.

Please post the code containing the problem line, plus the actual name of the sheet.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Well, the code in that place is the following

----------------------------------------
'v_Sheetname = "Data Input " & Date
'Sheets(v_Sheetname).Activate
'-----------------------------------------

The name of the sheet is like >>Data Input 04.11.2015<<.
The objective of that code is to activate the sheet with today's date, which holds yesterday's data.
The second line (with the Activate keyword) is underlined when the error occurs.

Thanks a lot!

So, given that today's date is 05.11.2015, if a sheet named "Data Input 05.11.2015" doesn't exist, you'll get a 'subscript out of range' error... There's also the question of whether the Date keyword on your system returns dates expressed in the DD.MM.YYYY format.

Even so, you shouldn't need to activate a worksheet to work with it. For example:

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Well,

the sheet does exist at the time I run my macro - it is prepared every day before 8 o' clock.
The output of the DATE function is formatted like that, at least it is when I output it in a MsgBox.
You're right, I don't need to activate the sheet, just like I don't need to SELECT cells all the time just to put some values into them. It just makes the code a lot easier to read (for not-savvy others), which is one of my primary development standards.

So, what can I do, or how could I make sure the date is formatted like I need it?

Thanks a lot!

The code I posted can be used just as easily to activate a sheet - just change:
MsgBox Sheets(v_Sheetname).Range("A1").Value
to:
Sheets(v_Sheetname).Range("A1").Activate

As I said, the issue may well be with what your code returns as the date.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

That may well be - and how could I change that?
(if that is the case, your code would not work any better in my instance as it also uses that variable, no?)

You might at least try the code I've posted and suggested.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

I have. It returns the exact same error, just like I said.

It works for me...

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Well, I thought so ... do you put that sheetname_variable in quotes or something?
I use the code
>>Sheets(v_Sheetname).Activate<<
which doesn't work.
The formatting of the date in a MsgBox is the exact same as on the sheet. Possibly the internal formatting that is used for the variable is different?
How could I control that?

The error is caused by no existing sheet having a name exactly the same as the variable string.
I'd be looking for extra spaces in one or the other.