0

I mean, I know how to use On Error Resume Next to solve this problem. But I just don't understand why this error will happen. Why excel compiler won't return Nothing if it can't find certain worksheet.... Do I misunderstand some concept?

Jing He
  • 794
  • 1
  • 9
  • 17

2 Answers2

2

Yes that will error out if Sheets("Output") is not really existing.
You are trying to access an object that does not exist.

Try this:

Dim wsop As Worksheet

On Error Resume Next
Set wsop = Sheets("Output")
On Error Goto 0

If wsop Is Nothing Then
    Set wsop = Sheets.Add(, Sheets(Sheets.Count)) 'after last sheet
    wsop.Name = "Output"
End If

Take note that we use OERN and OEG0 to suppress error during variable assignment.
To know more about error handling check out the link. HTH.

Community
  • 1
  • 1
L42
  • 19,427
  • 11
  • 44
  • 68
0

Try this

Sub test()

Dim wsSheet As Worksheet
On Error Resume Next
Set wsSheet = Sheets("Output") 'declare before check
On Error GoTo 0
If wsSheet Is Nothing Then
 Worksheets.Add.Name = "Output"
End If

End Sub
Kᴀτᴢ
  • 2,146
  • 6
  • 29
  • 57