Need to search workbook for NEW worksheet
 

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

--
Message posted from http://www.ExcelForum.com

Need to search workbook for NEW worksheet
 

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


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"RogerDaShrubber " wrote in
message ...
Here is the situation.

I have a work book that by pushing buttons a macro uses data in the
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 is
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 the
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 exists
with that name) it just comes to the point where the macro names the
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 ws
because I have not saved the program yet?

please help.


---
Message posted from http://www.ExcelForum.com/

Need to search workbook for NEW worksheet
 

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

--
Message posted from http://www.ExcelForum.com