Loop until a unique worksheet name is entered

[mikeburg]

I am trying to create a copy of a worksheet called TEMPLET & name the new worksheet a month end date like 63005.

I am having trouble getting the code to loop until a unique name (month end date) is entered in the input box.

The code loops correctly as long as there is a duplication, but stops when a unique name (month end date) is entered.

My current code is below, but I have tried loop variations with no success. Any ideas?

Thanks a million,

mikeburg

Sub createworksheet()
Dim monthenddate As String
Dim monthendname As String
monthenddate = Application.InputBox("Enter month end date ex-08-31-05: ")
monthendname = Replace(monthenddate, "-", "")
If monthenddate = "" Then GoTo done
duped:
If monthendname = Sheets(monthendname).Name Then
monthenddate = Application.InputBox("A worksheet aready exists for " & monthendname & ", enter a different date or cancel ex-08-31-05: ")
monthendname = Replace(monthenddate, "-", "")
If monthenddate = "" Then GoTo done
End If
If monthendname = Sheets(monthendname).Name Then GoTo duped
Sheets("Templet").Copy Before:=Sheets(1)
Sheets("Templet (2)").Name = monthendname
Sheets(monthendname).Range("H1") = monthenddate
done:
End Sub

[bhofsetz]

What do you mean the code 'stops' if you enter a unique name?

In testing your code I get a subscript out of range error when the sheet does not exist.

I would suggest putting in an error handler before your comparison line that will throw an error. Then you can create your new sheet.

Give this modification of your code a try

Sub createworksheet()
Dim monthenddate As String
Dim monthendname As String
monthenddate = Application.InputBox("Enter month end date ex-08-31-05: ")
monthendname = Replace(monthenddate, "-", "")
If monthenddate = "" Then Exit Sub
On Error GoTo NewSheet
duped:
If monthendname = Sheets(monthendname).Name Then
monthenddate = Application.InputBox("A worksheet aready exists for " & monthendname & ", enter a different date or cancel ex-08-31-05: ")
monthendname = Replace(monthenddate, "-", "")
If monthenddate = "" Then Exit Sub
End If
If monthendname = Sheets(monthendname).Name Then GoTo duped
Exit Sub
NewSheet:
Sheets("Templet").Copy Before:=Sheets(1)
Sheets("Templet (2)").Name = monthendname
Sheets(monthendname).Range("H1") = monthenddate
End Sub

HTH

[mikeburg]

Hey, Thanks,

mikeburg

[Dave Peterson]

One way:

Option Explicit
Sub createworksheet()

Dim monthenddate As String
Dim monthendname As String
Dim TestWks As Worksheet
Dim msg As String

msg = "Month ending date"

Do
monthenddate = InputBox(prompt:="Enter month end date ex-08-31-05:", _
Title:=msg)

monthendname = Replace(monthenddate, "-", "")

If monthenddate = "" Then
Exit Sub
End If

Set TestWks = Nothing
On Error Resume Next
Set TestWks = Worksheets(monthendname)
On Error GoTo 0

If TestWks Is Nothing Then
'that name doesn't exist, so get out
Exit Do
End If

msg = "Please use a different date!"
Loop


Sheets("Templet").Copy _
Before:=Sheets(1)

With ActiveSheet
.Name = monthendname
.Range("H1") = monthenddate
End With

End Sub


mikeburg wrote:
>
> I am trying to create a copy of a worksheet called TEMPLET & name the
> new worksheet a month end date like 63005.
>
> I am having trouble getting the code to loop until a unique name (month
> end date) is entered in the input box.
>
> The code loops correctly as long as there is a duplication, but stops
> when a unique name (month end date) is entered.
>
> My current code is below, but I have tried loop variations with no
> success. Any ideas?
>
> Thanks a million,
>
> mikeburg
>
> Sub createworksheet()
> Dim monthenddate As String
> Dim monthendname As String
> monthenddate = Application.InputBox("Enter month end date
> ex-08-31-05: ")
> monthendname = Replace(monthenddate, "-", "")
> If monthenddate = "" Then GoTo done
> duped:
> If monthendname = Sheets(monthendname).Name Then
> monthenddate = Application.InputBox("A worksheet aready exists for
> " & monthendname & ", enter a different date or cancel ex-08-31-05: ")
> monthendname = Replace(monthenddate, "-", "")
> If monthenddate = "" Then GoTo done
> End If
> If monthendname = Sheets(monthendname).Name Then GoTo duped
> Sheets("Templet").Copy Before:=Sheets(1)
> Sheets("Templet (2)").Name = monthendname
> Sheets(monthendname).Range("H1") = monthenddate
> done:
> End Sub
>
> --
> mikeburg
> ------------------------------------------------------------------------
> mikeburg's Profile: http://www.excelforum.com/member.php...o&userid=24581
> View this thread: http://www.excelforum.com/showthread...hreadid=387346

--

Dave Peterson