Need to search workbook for NEW worksheet

[RogerDaShrubber]

Here is the situation.

I have a work book that by pushing buttons a macro uses data in th
workbook and create a worksheet with that has that data graphed on it


I want the macro to first look for the sheet, to make sure that it i
not already created before it runs. I am currently using this code:

Dim wSht As Worksheet
Dim shtName As String
shtName = Format(Now, "mmmm_yyyy")
For Each wSht In Worksheets
If wSht.Name = shtName Then
MsgBox "Sheet already exists...Make necessary " & _
"corrections and try again."
Exit Sub
End If
Next wSht

to seach through the workbook for different sheets. So when I run th
code (and the ws does not exist yet) it works fine.

But when I run the macro a second time (and now a sheet already exist
with that name) it just comes to the point where the macro names th
chart, and stoppes there. and does not error.

When I debug the code I can see the "wSht.Name" skip over the new ws.
Is there any chance that "Worksheets" does not contain the new w
because I have not saved the program yet?

please help

 

[Rob van Gelder]

Sub test()
Dim wks As Worksheet, strName As String

strName = Format(Now, "mmmm_yyyy")

On Error Resume Next
Set wks = Worksheets(strName)
If Err.Number Then
Set wks = Worksheets.Add
wks.Name = strName
Err.Clear
Else
MsgBox "Sheet already exists...Make necessary " & _
"corrections and try again."
End If
On Error GoTo 0
End Sub

 

[RogerDaShrubber]

Thank you that does detect a ws with the same name, but is there a wa
to do this with out creating the new ws "MMMM_YYYY"?
thanks again