If worksheet name exists? 2

[eti3nne]

I create a new worksheet based on the currently active sheet's name eg wc08Dec08 plus 28 days gives wc05Jan09 etc.

If the new name already exists, I get a warning message, the macro fails and a new sheet called eg Sheet 1 is created.

How can I write an 'if exists statement' to capture this condition and warn the user that they have tried to create a sheet already available and also to delete the new 'Sheet1'. (NB. This Sheet number may be variable?)

Any ideas.

Thanks for looking.

 

[PHV]

What is your actual code that creates the sheet ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[eti3nne]

Now with code:

I create a new worksheet based on the currently active sheet's name eg wc08Dec08 plus 28 days gives wc05Jan09 etc.

If the new name already exists, I get a warning message, the macro fails and a new sheet called eg Sheet 1 is created.

How can I write an 'if exists statement' to capture this condition and warn the user that they have tried to create a sheet already available and also to delete the new 'Sheet1'. (NB. This Sheet number may be variable?)

Code:

' Divide up the sheetname into constituent data parts
Dim mday, mMonth, myear As String
mday = Mid(ActiveSheet.Name, 3, 2)
mMonth = Mid(ActiveSheet.Name, 5, 3)
myear = Mid(ActiveSheet.Name, 8, 2)

'Rebuild the date parts into a date
Dim mDate As Date
mDate = DateValue(mday & " " & mMonth & " " & myear)

'Construct the new sheet name
Dim newsheetname As String
newsheetname = "wc" & Format(mDate + 28, "ddMMMyy")


.......'''''If exists(newsheetname) Thensend warning message and don't allow sheet creation OR delete it once new sheetname created has been identified....
'''''Else.....
'''''End If

ActiveWorkbook.Worksheets.Add After:=ActiveSheet


'Move to the newly created sheet
ActiveSheet.Name = "wc" & Format(mDate + 28, "ddMMMyy")..

etc

 

[SkipVought]

Hi,

    Dim ws As Worksheet, bUniqueName As Boolean
    bUniqueName = True
    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name = NewSheet Then bUniqueName = False
    Next
    If bUniqueName Then _
        ActiveWorkbook.Worksheets.Add After:=ActiveSheet

ALSO, your declarations...

Dim mday, mMonth, myear As String

mday is Variant by default as is mMonth.

To declare ALL as string...

Dim mday as string, mMonth as string, myear As String

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[DaveInIowa]

Another technique would be to assign your new sheet name to the worksheet variable and trap for any errors:

    Dim ws As Worksheet
    
    On Error Resume Next
    Set ws = ActiveWorkbook.Worksheets(newSheetName)
    On Error GoTo 0
    
    If (ws Is Nothing) Then
        ActiveWorkbook.Worksheets.Add After:=ActiveSheet
        Set ws = ActiveSheet
        ws.Name = newSheetName
    Else
        MsgBox "Worksheet '" & newSheetName & "' already exists"
    End If

 

[eti3nne]

Like a charm....

DaveInIowa
Used your code in the end as I got a bit tangled with putting my code around the For Next loops.

Thanks to skipvought also for advice on declarations.

Cheers both.

Steve.