I got it. I failed to put the below part of the code
above the rest of the code.

Set TestWks = Nothing

Thanx

Thats very useful.

-----Original Message-----
It doesnt seem to be working. What am I doing wrong?


Private Sub CommandButton1_Click()
If MsgBox("This will save " & Worksheets(1).Range
("E2").Value & _
" Stats as New Worksheet named " & Worksheets(1).Range
("E2").Value & _
". Are you sure?", vbYesNo) = vbNo Then
Exit Sub
End If

Dim sha As Worksheet
Dim shar As Worksheet


Dim TestWks As Worksheet

Set TestWks = Nothing
On Error Resume Next
Set TestWks = Worksheets(sha.Range("E2").Value)
On Error GoTo 0

If TestWks Is Nothing Then
'everything is ok and continue
Else
MsgBox "already exists warning message"
Exit Sub '???
End If




Set sha = Worksheets(1)
'Set shar = ActiveWorkbook.Worksheets.Add
With ActiveWorkbook.Worksheets
Set shar = .Add(after:=.Item(.Count))
End With

sha.Cells.Copy

shar.Cells.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False

shar.Cells.PasteSpecial Paste:=xlPasteFormats, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False

shar.Name = sha.Range("E2").Value
shar.Range("A2").Select

ActiveWindow.DisplayZeros = False
Worksheets(1).Select

If MsgBox("Sheet Created Successfully. Would you
like
to save Workbook?", vbYesNo) = vbNo Then
Exit Sub
End If

ActiveWorkbook.Save
End Sub
.