Type Mismatch

[AngelK]

I have a code that creates a new sheet for each list of names in Sheet1. The problem I'm having is when the name has an apostrophe in it (such a O'Donnell), I'm getting a type mismatch error here:

If Not Evaluate("ISREF('" & CStr(N.Text) & "'!A1)")

Any suggestions for this VB newbie? Thanks

Sub CreateReportCards()
    Dim wsTch As Worksheet, wsTmp As Worksheet
    Dim shtNames As Range, N As Range

    Set wsTmp = Sheets("Template")
    Set wsTch = Sheets("Teacher")
    Set shtNames = wsTch.Range("C2:C" & Rows.Count).SpecialCells(xlConstants)
    
Application.ScreenUpdating = False
    For Each N In shtNames
        If Not Evaluate("ISREF('" & CStr(N.Text) & "'!A1)") Then
          wsTmp.Copy After:=Sheets(Sheets.Count)
            ActiveSheet.name = CStr(N.Text)
              ActiveSheet.Range("C5") = CStr(N.Text)
              End If
         Next N

wsTch.Select
End Sub

 

[Domenic]

Try the following...

If Not Evaluate("ISREF('" & [COLOR=#ff0000]Replace(N.Text, "'", "''")[/COLOR] & "'!A1)") Then

Hope this helps!