Setting worksheet name to variable doesn't work

[mark hansen]

I have the following code that seemed to stop working...

Dim ws, bf As Worksheet

'On Error GoTo ErrHandler
Set ws = Sheets(8)
Set bf = Sheets(9)

When I step through it (F8) I get a Error 9 Subscript out of range. The code is on sheet 8 and I want to set WS as sheet 8 and BF as sheet 9.

What could cause this to stop working. It was working when I wrote and stepped through it before?

Thanks,
Mark

 

[Norie]

Mark

How many worksheets do you have in the workbook and what are their names?

 

[mark hansen]

I have four work sheets 10, 5, 8, 9

In order above Configuration, DashBoard, New Data, Blue form.

I find it interesting before I removed the older worksheets, the code worked as expected. I removed the older data sheets because they were large and out of date.

 

[dmt32]

If you have named your worksheets with a numeric value only then you will need to coerce it in your code to a string value otherwise, code treats it as an index value which may explain why you get the error message.

Try

Set ws = Sheets(CStr(8))

and see if this solves your problem

Dave

 

[Norie]

Mark

What are the worksheet names?

Is it 10, 5, 8, 9 or 'Configuration', 'DashBoard', 'New Data', 'Blue form'?

 

[mark hansen]

Sheet 10 is "Configuration"
sheet 5 is "Dashboard"
Sheet 8 is "New Data"
Sheet 9 is "Blue Form"

The Cstr does work, but I don't want to have numbers as the worksheet name. Wouldn't it bring be right to messing with the code if the sheet name changes? That's why I like to go with the Sheet number when I can.

This is a strange one to me, I don't know why deleting other worksheets would break something that seems so basic. In this case I started with an existing workbook from someone... I think I'll start from scratch.

 

[Rick Rothstein]

Sheet 10 is "Configuration"
sheet 5 is "Dashboard"
Sheet 8 is "New Data"
Sheet 9 is "Blue Form"

The Cstr does work, but I don't want to have numbers as the worksheet name. Wouldn't it bring be right to messing with the code if the sheet name changes? That's why I like to go with the Sheet number when I can.

This is a strange one to me, I don't know why deleting other worksheets would break something that seems so basic. In this case I started with an existing workbook from someone... I think I'll start from scratch.

When you place a number between the parentheses of the Sheet object, that is an index to the displayed sheets, it is not sheet name. When you say Sheet(8), you are telling it to count over 8 sheets from the left and use that worksheet. Since you only have 4 worksheets, Excel cannot count over that many sheets, so it issued the subscript out of range. Your worksheet is (apparently) named "New Data", so that, quoted, is what should go between the parentheses... Sheet("New Data").

 

[dchaney]

just use the Sheet8 or Sheet9 these names never change no matter what order they are or what "Name" you give the sheet

 

[Rick Rothstein]

just use the Sheet8 or Sheet9 these names never change no matter what order they are or what "Name" you give the sheet

Unfortunately, the user can change those (code names) if he/she wants with very little trouble.

 

[mark hansen]

Ah no ()... I'll try that (not at the PC now.