IsError VBA for a non numeric logic

[JoshuaD]

Does anyone know how to make this work?

Sub Test1 ()

Dim ShtName As String
Dim ShtError As String

If IsError(Sheets(ShtName).Copy) = True Then

ShtError = ShtName & " cannot be copied due to it not existing"
GoTo Quit2:

Else

Sheets(ShtName).Copy
MsgBox(Shtname & " successfully copied")
GoTo Quit:

End IF

Quit2:
Msgbox(ShtError)

Quit:

End Sub

This error would happen if the variable ShtName was empty, or did not contain a valid sheet name. I am trying to create a custom error message for this exact error. There is a possibility of getting the runtime error in other locations in the code. So I cant just use any runtime errors.

Thank you all!

 

[Kenneth Hobson]

You have no string value so that would always error. Best to check if sheet exists first. e.g.

Sub Test_WorkSheetExists()
  MsgBox "WorksheetExists? " & WorkSheetExists("Sheet1"), _
    vbInformation, "ActiveWorkbook.ActiveSheet"
    
   MsgBox "WorksheetExists? " & WorkSheetExists("ken", "ken.xlsm"), _
    vbInformation
End Sub

 'WorkSheetExists in a workbook:
Function WorkSheetExists(sWorkSheet As String, Optional sWorkbook As String = "") As Boolean
    Dim ws As Worksheet, wb As Workbook
    On Error GoTo notExists
    If sWorkbook = "" Then
      Set wb = ActiveWorkbook
      Else
      Set wb = Workbooks(sWorkbook) 'sWorkbook must be open already.  e.g. ken.xlsm, not x:\ken.xlsm.
    End If
    Set ws = wb.Worksheets(sWorkSheet)
    WorkSheetExists = True
    Exit Function
notExists:
    WorkSheetExists = False
End Function

 

[JoshuaD]

ok thank you!