JimS
07-10-2009, 10:24 AM
How can this code be modified so that it will only change certain sheets, in example sheets 1 – 15, and only if they exist, there might not be 15 sheets (the sheets get created due to an import).
There are other sheets in the workbook that I do not want this code to change, for example sheet 16 – 20 do not need to be renamed.
To make it worst I want the sheet name to be the result of this formula:
=IF(ISERR(FIND(".",B1,2+FIND("\",B1))),"x",MID(B1,FIND("\",B1)+2,FIND(".",B1,2+FIND("\",B1))-FIND("\",B1)-2))
This formula is not on the sheet so VBA needs to execute it.
Sub RenameTabs()
' Renames all worksheet tabs with each worksheet's cell A1 contents.
'If cell A1 has no content, then that tab is not renamed.
For i = 1 To Sheets.Count
If Worksheets(i).Range("A1").Value <> "" Then
Sheets(i).Name = Worksheets(i).Range("A1").Value
End If
Next
End Sub
Thanks…
Jim
There are other sheets in the workbook that I do not want this code to change, for example sheet 16 – 20 do not need to be renamed.
To make it worst I want the sheet name to be the result of this formula:
=IF(ISERR(FIND(".",B1,2+FIND("\",B1))),"x",MID(B1,FIND("\",B1)+2,FIND(".",B1,2+FIND("\",B1))-FIND("\",B1)-2))
This formula is not on the sheet so VBA needs to execute it.
Sub RenameTabs()
' Renames all worksheet tabs with each worksheet's cell A1 contents.
'If cell A1 has no content, then that tab is not renamed.
For i = 1 To Sheets.Count
If Worksheets(i).Range("A1").Value <> "" Then
Sheets(i).Name = Worksheets(i).Range("A1").Value
End If
Next
End Sub
Thanks…
Jim