new sheet with same name as previous.

[GeneWan]

if I execute a macro and it opens a sheet, how can I name the sheet to be of
the same name but this time with the following:

ex. first execute, filename is "My sheet"
second execute, filename becomes "My sheet (2)"

[Greg Wilson]

There are three takes on your post:
1. You are refering to a worksheet and by "it opens a sheet" you mean
activate the sheet.
2. You are creating a new worksheet and not just activating and renaming an
existing sheet.
3. You are refering to opening and renaming a workbook.

The appended code assumes you mean the first of the above interpretations:

Sub RenameSheet()
Dim ws As Worksheet
Dim x As Integer
Dim txt As String

Set ws = Sheets(1)

'Code that activates the sheet and other stuff here

txt = ws.Name
Select Case txt
Case "My Sheet"
txt = "My Sheet (2)"
Case Else
x = InStrRev(txt, "(")
If x = 0 Then
txt = "My Sheet"
Else
txt = Left(txt, x) & CInt(Mid(txt, x + 1, 1)) + 1 & ")"
End If
End Select
ws.Name = txt
End Sub

If you mean open and rename a file then I think I would use similar logic
but would rename it before opening it or after closing as opposed to using
SaveAs. The following only demos using the Name command and assumes you will
construct the logic. Note how simple it is:

Dim pth As String
pth = ThisWorkbook.Path
Name pth & "\My Sheet.xls" As pth & "\My Sheet (2).xls"

Regards,
Greg


"GeneWan" wrote:

> if I execute a macro and it opens a sheet, how can I name the sheet to be of
> the same name but this time with the following:
>
> ex. first execute, filename is "My sheet"
> second execute, filename becomes "My sheet (2)"

[GeneWan]

thanks greg~

"Greg Wilson" wrote:

> There are three takes on your post:
> 1. You are refering to a worksheet and by "it opens a sheet" you mean
> activate the sheet.
> 2. You are creating a new worksheet and not just activating and renaming an
> existing sheet.
> 3. You are refering to opening and renaming a workbook.
>
> The appended code assumes you mean the first of the above interpretations:
>
> Sub RenameSheet()
> Dim ws As Worksheet
> Dim x As Integer
> Dim txt As String
>
> Set ws = Sheets(1)
>
> 'Code that activates the sheet and other stuff here
>
> txt = ws.Name
> Select Case txt
> Case "My Sheet"
> txt = "My Sheet (2)"
> Case Else
> x = InStrRev(txt, "(")
> If x = 0 Then
> txt = "My Sheet"
> Else
> txt = Left(txt, x) & CInt(Mid(txt, x + 1, 1)) + 1 & ")"
> End If
> End Select
> ws.Name = txt
> End Sub
>
> If you mean open and rename a file then I think I would use similar logic
> but would rename it before opening it or after closing as opposed to using
> SaveAs. The following only demos using the Name command and assumes you will
> construct the logic. Note how simple it is:
>
> Dim pth As String
> pth = ThisWorkbook.Path
> Name pth & "\My Sheet.xls" As pth & "\My Sheet (2).xls"
>
> Regards,
> Greg
>
>
> "GeneWan" wrote:
>
> > if I execute a macro and it opens a sheet, how can I name the sheet to be of
> > the same name but this time with the following:
> >
> > ex. first execute, filename is "My sheet"
> > second execute, filename becomes "My sheet (2)"

[Greg Wilson]

I see I had an oversight. If you execute more than 9 times then the code I
gave you won't handle double digit numbers. Try this instead:

Sub RenameSheet()
Dim ws As Worksheet
Dim x As Integer, x2 As Integer
Dim txt As String

Set ws = Sheets(1)

'Code that activates the sheet and other stuff here

txt = ws.Name
Select Case txt
Case "My Sheet"
txt = "My Sheet (2)"
Case Else
x = InStrRev(txt, "(")
x2 = InStrRev(txt, ")")
If x = 0 Then
txt = "My Sheet"
Else
txt = Left(txt, x) & _
CInt(Mid(txt, x + 1, x2 - x - 1)) + 1 & ")"
End If
End Select
ws.Name = txt
End Sub

Regards,
Greg

"GeneWan" wrote:

> thanks greg~
>
> "Greg Wilson" wrote:
>
> > There are three takes on your post:
> > 1. You are refering to a worksheet and by "it opens a sheet" you mean
> > activate the sheet.
> > 2. You are creating a new worksheet and not just activating and renaming an
> > existing sheet.
> > 3. You are refering to opening and renaming a workbook.
> >
> > The appended code assumes you mean the first of the above interpretations:
> >
> > Sub RenameSheet()
> > Dim ws As Worksheet
> > Dim x As Integer
> > Dim txt As String
> >
> > Set ws = Sheets(1)
> >
> > 'Code that activates the sheet and other stuff here
> >
> > txt = ws.Name
> > Select Case txt
> > Case "My Sheet"
> > txt = "My Sheet (2)"
> > Case Else
> > x = InStrRev(txt, "(")
> > If x = 0 Then
> > txt = "My Sheet"
> > Else
> > txt = Left(txt, x) & CInt(Mid(txt, x + 1, 1)) + 1 & ")"
> > End If
> > End Select
> > ws.Name = txt
> > End Sub
> >
> > If you mean open and rename a file then I think I would use similar logic
> > but would rename it before opening it or after closing as opposed to using
> > SaveAs. The following only demos using the Name command and assumes you will
> > construct the logic. Note how simple it is:
> >
> > Dim pth As String
> > pth = ThisWorkbook.Path
> > Name pth & "\My Sheet.xls" As pth & "\My Sheet (2).xls"
> >
> > Regards,
> > Greg
> >
> >
> > "GeneWan" wrote:
> >
> > > if I execute a macro and it opens a sheet, how can I name the sheet to be of
> > > the same name but this time with the following:
> > >
> > > ex. first execute, filename is "My sheet"
> > > second execute, filename becomes "My sheet (2)"