Rename a worksheet after copy...

[Guest]

Hello. I am using the following code to copy a worksheet named DOS5 into a
workbook that already has a sheet called DOS5. When it gets copied I would
like to rename it to the next sequential sheet number (ie: DOS6). Can
someone help?

bk.Sheets("DOS5").Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)

Thanks,
Cathy

 

[Guest]

I think this is what you are looking for...

Sub test()
Dim strNewName As String
strNewName = "DOS" & CInt(Mid("DOS5", 4, 3)) + 1

If SheetExists(strNewName, ThisWorkbook) Then
MsgBox strNewName & " already exists."
Else
Sheets("DOS5").Copy
After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
ActiveSheet.Name = strNewName
End If

End Sub

Public Function SheetExists(SName As String, _
Optional ByVal Wb As Workbook) As Boolean
'Chip Pearson
On Error Resume Next
If Wb Is Nothing Then Set Wb = ThisWorkbook
SheetExists = CBool(Len(Wb.Sheets(SName).Name))
End Function

 

[Ron de Bruin]

Hi Cathy are the first three characters always "DOS"

Is you copy DOS7 you want to name it DOS8
Or do you always copy DOS5 ?

 

[Bob Phillips]

Dim num As Long
Dim fOK As Boolean
Dim sh As Worksheet

num = 5
fOK = False
Do
Set sh = Nothing
On Error GoTo 0
Set sh = ThisWorkbook.Sheets("DOS" & num)
On Error GoTo 0
fOK = Not sh Is Nothing
If fOK Then
bk.Sheets("DOS" & num).Copy
After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
Else
num = num + 1
End If
Loop Until fOK

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)