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!

 

[r3dal3rt]

Forgive a stupid question, but bearing in mind this comment

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

I deleted all the sheets except for Summary sheet. In the Summary sheet, I deleted a few employee's names (thus confirming it is not employee 14's name that is the source of the issue).
Macro 1 looks through the list of employees, verifies that a sheet with the employee's name exists, creates new sheets as needed.
Macro 2 (the one I am having issues with) is suppose to copy and paste some other information from the Summary sheet to the respective employee's sheet

The error occurs at the "With wsName" block. With regards to Set wsName = ThisWorkbook.Worksheets(sName) : When I hover over "sName" it shows the employee's name but when I hover over "wsName", it doesnt show anything.

I hope that made sense...

 

[Fluff]

Could you post the complete code for that macro?

 

[r3dal3rt]

Could you post the complete code for that macro?

So there is a date at A8 and the list of employee names start at A10

Sub TransferData()

'### Copies data from Summary sheet to each employee's sheet ###


Dim wsSummary, wsName As Worksheet
Dim sName As String
Dim i, iLastRow, iDate, iPrevMth, iPrevMthRow As Long

Set wsSummary = ThisWorkbook.Worksheets("Summary")

With wsSummary
    iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    iDate = .Range("A8")
    PrevMth = Application.EoMonth(iDate, -1)

    For i = 10 To iLastRow
        sName = .Range("A" & i).Value
            Set wsName = ThisWorkbook.Worksheets(sName)

                'ERROR 91 HERE
                With wsName
                    iPrevMthRow = .Columns("B").Find(what:=CDate(PrevMth), LookIn:=xlValues).Row     'search for previous mth
                    .Rows(iPrevMthRow).Copy
                    .Rows(iPrevMthRow + 1).PasteSpecial xlPasteAll
                    .Range("B" & iPrevMthRow + 1).Formula = "=EOmonth(R[-1]C,1)"
                End With

            Next i

End With


End Sub

 

[RoryA]

I suspect your error is actually caused by your find failing here:

iPrevMthRow = .Columns("B").Find(what:=CDate(PrevMth), LookIn:=xlValues).Row

 

[Fluff]

Beaten 2 it