Error 91: with block and setting worksheet name

[r3dal3rt]

Hi forum, I am having a weird problem that I've been struggling for a couple of weeks now.

I have a list of employee names in Column A and a a For loop that sets the worksheet name ("wsName") to each cell value (employee names).
The macro works fine for the first 13 employees; on the 14th employee, I get Error 91. For whatever reason, on employee 14, it is not setting "wsName". The 14th employee name doesnt have any symbols or numbers.

Dim wsSummary, wsName As Worksheet
Dim sName As String

Set wsSummary = ThisWorkbook.Worksheets("Summary")

With wsSummary[INDENT]For i = 1 to 25[/INDENT]
[INDENT]        sName = .Range("A" & i).Value[/INDENT]
[INDENT=2]Set wsName = ThisWorkbook.Worksheets(sName)

With wsName[/INDENT]
[INDENT=3]'Some copy and paste stuff
End With[/INDENT]
[INDENT]
Next i[/INDENT]
End With
End Sub

Thanks in advance for your help!

 

[Fluff]

Check that the sheet name & Employee name are the same & check that there are no leading/trailing spaces.
Also what is the length of that employee's name.

 

[r3dal3rt]

Check that the sheet name & Employee name are the same & check that there are no leading/trailing spaces.
Also what is the length of that employee's name.

Hey Fluff, I also have a macro before this macro that checks for sheets. If the sheet doesn't exist (i.e. new employee), it would create the sheet. The employee name is 12 characters. I used LEN() to check there are no leading/trailing spaces.

Thanks!

 

[Fluff]

If you try this what does the msgbox say?

Sub Chk()
MsgBox "|" & Sheets("Summary").Range("A14").Value & "|"
End Sub

 

[r3dal3rt]

Just another thought. I deleted the 14th employee's name (so the 15th employee is now 14th), and I get the same error. then I deleted a few more names...
It seems like this error with not setting wsName is happening on the 14th employee regardless of the string.

 

[r3dal3rt]

If you try this what does the msgbox say?

Sub Chk()
MsgBox "|" & Sheets("Summary").Range("A14").Value & "|"
End Sub

Message box shows |Jack Smith|

Just another thought. I deleted the 14th employee's name (so the 15th employee is now 14th), and I get the same error. then I deleted a few more names...
It seems like this error with not setting wsName is happening on the 14th employee regardless of the string.

 

[Youngdand]

Does the data natively come from excel? or is it extracted from another source? Potentially could be something like a no breaking space in the name rather than an ordinary space.

 

[r3dal3rt]

Does the data natively come from excel? or is it extracted from another source? Potentially could be something like a no breaking space in the name rather than an ordinary space.

Data is native (i.e. names are manually entered into the spreadsheet).

 

[RoryA]

You shouldn't get an error 91 if the sheet name were incorrect - you'd get a subscript out of range error (error 9).

Which line actually causes the error, and what is the code in the loop?

 

[Fluff]

Forgive a stupid question, but bearing in mind this comment

Just another thought. I deleted the 14th employee's name (so the 15th employee is now 14th), and I get the same error. then I deleted a few more names...
It seems like this error with not setting wsName is happening on the 14th employee regardless of the string.

Do you have more than 13 sheets + the "Summary" sheet in that workbook?