Hi
you can test existence with this

'Tests to see if a worksheet with the given name exists in the active
workbook
Function IsSheetThere(shName As String) As Boolean
Dim DummyWks As String
IsSheetThere = False
On Error Resume Next
DummyWks = ActiveWorkbook.Worksheets(shName).Name
If Err.Number = 0 Then IsSheetThere = True
End Function

and use it as

If not isSheetThere(ShtName) then
Set wsNew =
ActiveWorkbook.Worksheets.Add(after:=Worksheets(Ot herSheet) ) 'Makes
wsNew the active sheet
wsNew.Name = ShtName
end if

regards
Paul

On Feb 13, 10:32*am, Wesslan wrote:
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