First, I would add the sheet whereever I wanted and then just sort the sheets
after I'm done.

Chip Pearson has some code at:
http://www.cpearson.com/excel/sortws.htm
that you can use.

But this kind of routine can be used to add another sheet:

Option Explicit
Sub testme()

Dim mySFX As String
Dim myPFX As String
Dim wks As Worksheet
Dim iCtr As Long
Dim myStr As String

'01-01-06 CSB 18" RCP

mySFX = " CSB 18"" RCP"
myPFX = Trim(InputBox(prompt:="Enter date: mm-dd-yy"))

If Len(myPFX) < 8 Then
Beep
MsgBox "Try again later!"
Exit Sub
End If

Set wks = Worksheets.Add(after:=Worksheets(Worksheets.Count) )
iCtr = 0
Do
If iCtr = 0 Then
myStr = ""
Else
myStr = " (" & iCtr & ")"
End If
On Error Resume Next
wks.Name = myPFX & mySFX & myStr
If Err.Number < 0 Then
Err.Clear
Else
Exit Do
End If
On Error GoTo 0
iCtr = iCtr + 1
Loop

Call SortTheSheets

End Sub
Sub SortTheSheets()
'put Chip Pearson's code here
End Sub

jnf40 wrote:

Hi all,

I have a workbook that will add worksheets and name the worksheet depending
on the buttons clicked by the user and the date entered by the user. The
worksheet names will be something like "01-01-06 CSB 18" RCP" if the same
date is used the next worksheet will be "01-01-06 CSB 18" RCP (2)" and so on.
If the user changes the date to "01/02/06" then the next worksheet name would
be
"01-02-06 CSB 18" RCP" and so on. The problem I have is that if I then go
back to the date "01/01/06" I get "Run-time error '1004': Cannot rename a
sheet to the same name as another sheet, a referenced object library or a
workbook referenced by Visual Basic."
Is there a way to have it check for this and then add the new worksheet and
it's name would then be
"01-01-06 CSB 18" RCP (3)" and place it in between "01-01-06 CSB 18" RCP
(2)" and "01-02-06 CSB 18" RCP"?

--

Dave Peterson