Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rename a worksheet after copy...
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.Cou nt) Thanks, Cathy |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rename a worksheet after copy...
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.Cou nt) 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 -- HTH... Jim Thomlinson "Cathy W" wrote: 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.Cou nt) Thanks, Cathy |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rename a worksheet after copy...
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 ? -- Regards Ron de Bruin http://www.rondebruin.nl "Cathy W" wrote in message ... 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.Cou nt) Thanks, Cathy |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rename a worksheet after copy...
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.Cou nt) Else num = num + 1 End If Loop Until fOK -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Cathy W" wrote in message ... 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.Cou nt) Thanks, Cathy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro: Copy a worksheet and rename it to a specified value | Excel Discussion (Misc queries) | |||
Copy worksheet and rename sheet | Excel Discussion (Misc queries) | |||
Worksheet copy with rename | Excel Programming | |||
Copy/Paste Worksheet to last and rename with input box for value. | Excel Programming | |||
Copy worksheet from multiple files in one DIR to another DIR & rename | Excel Programming |