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)"

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)"

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)"

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)"