I have:

Dim SrcNm1 As String
Dim SrcNm2 As String
Dim WS1 As Worksheet, WS2 As Worksheet
SrcNm1 = "Sheet2"
SrcNm2 = "ClientLedgerOct31"

Set WS1 = ThisWorkbook.Worksheets(SrcNm1)
' WS1 is now successfully set as Worksheet/Sheet2
Set WS2 = ThisWorkbook.Worksheets(SrcNm2)
' This line throws up a Runtime error '9' - "Subscript out of range"
error

Why do I get the error when setting WS2 but not WS1? Is it because
there are too many letters in the sheet name?
The sheet with that name definitely exists, and the spelling is
correct etc.

Thanks

The sheet with that name definitely exists, and the spelling is
correct etc.

You may think so, but that error message pretty much says you are not
correct. Double check both the sheet's name and the string being assigned to
SrcNm2 and make sure there are no extra or missing spaces (look at the
beginning and end of the sheet's name in particular), make sure that upper
case "O" was really typed with the letter "O" and not the digit zero, make
sure the "1" is really the digit one and not a lower case "L", and make sure
the sheet's name used the "ie" letter combination and not "ei". Those look
to be the obvious places where an error could occur to me.

--
Rick Rothstein (MVP - Excel)



"ajlowndes" wrote in message
...
I have:

Dim SrcNm1 As String
Dim SrcNm2 As String
Dim WS1 As Worksheet, WS2 As Worksheet
SrcNm1 = "Sheet2"
SrcNm2 = "ClientLedgerOct31"

Set WS1 = ThisWorkbook.Worksheets(SrcNm1)
' WS1 is now successfully set as Worksheet/Sheet2
Set WS2 = ThisWorkbook.Worksheets(SrcNm2)
' This line throws up a Runtime error '9' - "Subscript out of range"
error

Why do I get the error when setting WS2 but not WS1? Is it because
there are too many letters in the sheet name?
The sheet with that name definitely exists, and the spelling is
correct etc.

Thanks