Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop until a unique worksheet name is entered
I am trying to create a copy of a worksheet called TEMPLET & name th new worksheet a month end date like 63005. I am having trouble getting the code to loop until a unique name (mont end date) is entered in the input box. The code loops correctly as long as there is a duplication, but stop when a unique name (month end date) is entered. My current code is below, but I have tried loop variations with n success. Any ideas? Thanks a million, mikeburg Sub createworksheet() Dim monthenddate As String Dim monthendname As String monthenddate = Application.InputBox("Enter month end dat 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 fo " & 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 Befo=Sheets(1) Sheets("Templet (2)").Name = monthendname Sheets(monthendname).Range("H1") = monthenddate done: End Su -- mikebur ----------------------------------------------------------------------- mikeburg's Profile: http://www.excelforum.com/member.php...fo&userid=2458 View this thread: http://www.excelforum.com/showthread.php?threadid=38734 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop until a unique worksheet name is entered
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 Code: -------------------- 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 Befo=Sheets(1) Sheets("Templet (2)").Name = monthendname Sheets(monthendname).Range("H1") = monthenddate End Sub -------------------- HTH -- bhofsetz ------------------------------------------------------------------------ bhofsetz's Profile: http://www.excelforum.com/member.php...o&userid=18807 View this thread: http://www.excelforum.com/showthread...hreadid=387346 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop until a unique worksheet name is entered
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 _ Befo=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 Befo=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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop until a unique worksheet name is entered
Hey, Thanks, mikeburg -- mikeburg ------------------------------------------------------------------------ mikeburg's Profile: http://www.excelforum.com/member.php...o&userid=24581 View this thread: http://www.excelforum.com/showthread...hreadid=387346 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Loop based on number entered in userform | Excel Worksheet Functions | |||
Stop unique values been entered | Excel Worksheet Functions | |||
Unique numbers entered once only | Excel Discussion (Misc queries) | |||
worksheet loop | Excel Programming | |||
How? Macro to copy range to new worksheet, name new worksheet, loop | Excel Programming |