Why cannot you set the rng to nothing before you do any tests? If the sheet
exists then assign rng else do nothing.

I hope I understood your issue.

--

Regards,
Nigel




"Wesslan" wrote in message
...
Hi,

I am very glad for all the support I have received over the last
couple of days. I have now a problem. Given that there is already an
existing sheet (in this case "Global - " & ConditionSize & " Banks" ),
the code works smoothly (thus not producing a new sheet since it is
already there). But if the sheet is not there, I want the rng to still
be Nothing and thus it should produce a new sheet. But if the macro
can't find the sheet in question it says "Run Time Error '9':
Subscript out of range". I understand the problem encountered, I just
don't know how to get around it. I'd rather not use On Error Resume
Next.

Any suggestions?

Code:

'Copies the RawData sheet to a new sheet
If Sht2 = "Global - " & ConditionSize & " Banks" Then

Set rng = Nothing
Set rng = Worksheets(Sht2)

If rng Is Nothing Then

Sheets(Sht).Copy After:=Sheets("Assumptions")
Sheets(Sht & " (2)").Name = Sht2
End If

Else:
Sheets(Sht).Copy After:=Sheets("Assumptions")
Sheets(Sht & " (2)").Name = Sht2
End If

Regards,
Peder