Runtime Error 9 - Subscript out of range

[starchildren3317]

Good Morning Community,

I have a worksheet named AppSelect and when it gets activated I am trying to copy a row from another worksheet within the same workbook to this active worksheet.

Here is the code that I have to do such:

Private Sub Worksheet_Activate()

With Worksheets("Skills").Range("A2")
    .Range(.Cells(1), .End(xlToRight)).Copy Destination:=Worksheets("AppSelect").Range("A1")
End With

End Sub

When I activate the sheet however, I get this error:

Run-time Error '9':
Subscript out of range

It seems to be failing out on this line:

With Worksheets("Skills").Range("A2")

The Skills worksheet does exist and it is not misspelled so I am not really sure what it is choking. Any ideas?

 

[texasalynn]

make sure the worksheet name is spelled correct "SKILLS"
and that the other worksheet name is correct and does exist

 

[starchildren3317]

It does, looking around with that error, I have seen many people suggesting the same thing that is why I double checked before posting and put down at the end:

The Skills worksheet does exist and it is not misspelled

I solved the issue, but I do not know why it wasn't working the way I had it.
Skills is the second worksheet in the workbook
So basically instead of this:

With Worksheets("Skills").Range("A2")

I wrote it as

With Worksheets(2).Range("A2")

And that seemed to solve everything. I would like to know what is going so that I don't have to rely on the "Skills" worksheet to be the second sheet.

 

[Norie]

Is 'Skills' definitely the name on the worksheet tab and not it's codename?

The codename is what you see in the VBE explorer before the worksheet tab name, which is in parentheses.

 

[Jonmo1]

Also, it's very common for there to be a SPACE at the end of the tab name.
So it still appears to be "Skills", when in fact it's "Skills "

What does this return

MsgBox "|" & Worksheets(2).Name & "|"

Is it |Skills|
or |Skills |
or | Skills|