changing sheet's name

[avishain]

hello all,

i work with excel 2007.

i got 1 workbook which contains couple of worksheets.

i want that for each worksheet-the name of that spesific sheet will change according to the content in cell L2,however,with a small change:

the number in cell L2 has always 7 digits and i need only 4 from the second digit,for instance, if the number is 1234567 - i need to extract 2345 and use it for the name of the sheet.

in some cases the same number could appear more than once and then ill have the same name of the worksheet so i dont want to have an error msg...if there's a duplicate name i wish to add (1) or (2) and so on...


for instance,

ill have 1 sheet called 2133 and the second will be 2133(1) ...


thank u very much

 

[texasalynn]

try this

Sub Rename_Sheet()
Dim sh As Worksheet, flg As Boolean
Dim i As Long
Dim wFile As String
wFile = Mid(Range("L2"), 2, 4)
i = 1
For Each sh In Worksheets
    If sh.Name Like wFile Then
        flg = True
        i = i + 1
    End If
Next
If flg = True Then
    ActiveWorksheet.Name = wFile & "(" & i + 1 & ")"
End If
End Sub

note: untested

 

[avishain]

try this

Sub Rename_Sheet()
Dim sh As Worksheet, flg As Boolean
Dim i As Long
Dim wFile As String
wFile = Mid(Range("L2"), 2, 4)
i = 1
For Each sh In Worksheets
    If sh.Name Like wFile Then
        flg = True
        i = i + 1
    End If
Next
If flg = True Then
    ActiveWorksheet.Name = wFile & "(" & i + 1 & ")"
End If
End Sub

note: untested

thanks but its not working....

 

[texasalynn]

thanks but its not working....

ok but that doesn't let me know how to help you? What happens - errors? Debug - which line? doesn't move the data? doesn't name the sheet? you get the idea