Sheet Name Problems

[Ann_BBO]

After Analysis, it will generate sheet name say STAFF and STAFF which is the file type of the Input File. If I click the CheckBox to open the newsheet. After that, if it assigns the new sheetname also belongs STAFF file type. I want to change this new sheet name called STAFF (2)..
Pattern:
Sheet 1=STAFF
Sheet 2=Member
Sheet 3=STAFF (2)
Sheet 4=Member (2)
Sheet 5=Member (3)
.
.
Overall say, if the newsheet name=old sheet name, then add the(2), (3) after the name. How to run this marco??

Thanks

[Ann_BBO]

As each sheet name extracted the Range("c1").value in each sheet.
Therefore, i try to the below command but it output the error 1004
[VBA] For Each sh In ThisWorkbook.Sheets
If sh.Name = Tgt.Range("c1").Value Then
For z = 2 To 20
.Name = Range("C1").Value + "(z)"
z = z + 1
Next
Else
.Name = Range("C1").Value
End If
Next[/VBA]
Thanks

[Charlize]

Maybe this ?[VBA]Option Explicit
Option Compare Text
Sub staff_or_member()
Dim sh As Worksheet
Dim Staffno As Long
Dim Memberno As Long
Staffno = 1
Memberno = 1
For Each sh In ThisWorkbook.Sheets
If sh.Range("C1").Value = "STAFF" And Left(sh.Name, 5) <> "STAFF" Then
If Staffno = 1 Then
sh.Name = "STAFF"
Staffno = Staffno + 1
Else
sh.Name = "STAFF (" & Staffno & ")"
Staffno = Staffno + 1
End If
ElseIf Left(sh.Name, 6) <> "Member" Then
Staffno = Staffno + 1
End If
If sh.Range("C1").Value = "Member" And Left(sh.Name, 6) <> "Member" Then
If Memberno = 1 Then
sh.Name = "Member"
Memberno = Memberno + 1
Else
sh.Name = "Member (" & Memberno & ")"
Memberno = Memberno + 1
End If
ElseIf Left(sh.Name, 5) <> "STAFF" Then
Memberno = Memberno + 1
End If
Next sh
End Sub[/VBA]

[Ann_BBO]

Actually, the range("C1") value is vary each time. It depends on user input the what files. Therefore, i don't know the name in the Range("c1").value.
Anyway thanks to u

Also, i try to write this vba to do the above action.
But it has problems. First is when z cannot change to 2,3,4... and it only show the value is z (e.g. STAFFz). Second problem is according the program, when i analyze data with even time,it will change the sheetname to STAFF in the first street.When run single time, it will change the sheetname to STAFFn in the first street.
[vba] Function bWorksheetExists(WSName As String) As Boolean
On Error Resume Next
bWorksheetExists = (ActiveWorkbook.Worksheets(WSName).Name = WSName)
End Function

With Tgt
.Activate

If bWorksheetExists(Range("C1").Value) Then
For z = 2 To 20
.Name = Range("C1").Value + " z"
z = z + 1
Next z
Else
.Name = Range("C1").Value
End If


End With[/vba]

[Charlize]

Actually, the range("C1") value is vary each time. It depends on user input the what files. Therefore, i don't know the name in the Range("c1").value.

Force them otherwise don't let them save it as a staff or member file. You can check on an empty value and that it must be STAFF or Member.

[Ann_BBO]

It maybe impossible. As the STAFF, Member just i create the name. I cannot let them to change the sheet name must be the above 2 name.

I say that my action again:
1st Sheet: If Range("c1")=ABC in 1st sheet, then 1st sheet.name=ABC
2nd Sheet: If Range("c1")=DEF in 2nd sheet, then 2nd sheet.name=DEF
3rd Sheet: If Range("c1")=ABC in 3rd sheet, then 3rd sheet.name=ABC (2)
4th Sheet: If Range("c1")=GHI in 4th sheet, then 4th sheet.name=GHI
5th Sheet: If Range("c1")=ABC in 5th sheet, then 5th sheet.name=ABC (3)
.
.
Anyway thanks to u !!

[Charlize]

Never say never. I guess that it's something like this that you need/want.
[vba]Option Explicit
Option Compare Text
Public sh_exists_no
Sub staff_or_member()
Dim sh As Worksheet
For Each sh In ThisWorkbook.Sheets
If sh.Range("C1").Value <> vbNullString And _
sh.Range("C1").Value <> Left(sh.Name, Len(sh.Range("C1").Value)) Then
Call sh_exists(UCase(sh.Range("C1").Value))
If sh_exists_no = 1 Then
sh.Name = UCase(sh.Range("C1").Value)
Else
sh.Name = UCase(sh.Range("C1").Value) & " (" & sh_exists_no & ")"
End If
End If
Next sh
End Sub
Sub sh_exists(sht_name As String)
Dim sht As Worksheet
sh_exists_no = 1
For Each sht In ThisWorkbook.Sheets
If Left(sht.Name, Len(sht_name)) = sht_name Then
sh_exists_no = sh_exists_no + 1
End If
Next sht
End Sub[/vba]

[Ann_BBO]

Sorry Charlize!!
Maybe i mis-know your meanings.
Don't care this as my english is very bad.
Anyway thank for your help

[Charlize]

Sorry Charlize!!
Maybe i mis-know your meanings.
Don't care this as my english is very bad.
Anyway thank for your help

Have you tried the last coding example. It's doing what you wanted based on not knowing what someone fills in as name. Try it with that ABC - stuff from your post.

[Ann_BBO]

It works.
Thank you for your help!!!
AND
Sorry for any misunderstanding from me!!

[Charlize]

It works.
Thank you for your help!!!
AND
Sorry for any misunderstanding from me!!

Well, most of the time the program does want I want it to do (otherwise it's the computer ...). Some characters aren't allowed for a sheet name (/, * and maybe some others). So if they aren't going to use such a character, it will work. You could do a check on every character and replace it with something else if it's one of those that aren't allowed. So I leave that up to you to solve that one. (Or you could limit the possible characters that someone may put in a cell (letters, space).