Solved: Macro with a Formula to rename sheets

[JimS]

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(".",B 1,2+FIND("\",B1))-FIND("\",B1)-2))

This formula is not on the sheet so VBA needs to execute it.



[VBA]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[/VBA]




Thanks…

Jim

[mdmackillop]

What is the typical text in B1, and what are you trying to extract from it? I assume you want to use the B1 on each sheet to name that sheet.

[JimS]

Correct, I want to use B1 from each Sheet to be it's own new Sheet Name. The reason for the formula is so that I can pull out the name that is between the \\ and .
So if \\PCS004.USA.01.West1 were in cell B1 then PCS004 would be the new Sheet Name

[JimS]

I was able to streamline the formula to =MID(B1,3,FIND(".",B1)-3) but I don't know how to use it in VBA using the WorksheetFunction.

[p45cal]

Is there something in common with the sheets that do not have to be renamed, for example, they may already have their own name which doesn't begin "Sheet"?
If that's the case, use that in vba not to rename the sheet. Below I used a criterion that says if the sheet name begins with "Sheet", then it's up for being renamed:[vba]Sub blah()
For Each sht In ActiveWorkbook.Sheets
xxx = Empty
If Left(sht.Name, 5) = "Sheet" Then
On Error Resume Next
xxx = Split(Split(sht.Range("B1").Value, "\\")(1), ".")(0)
On Error GoTo 0
If Not IsEmpty(xxx) Then sht.Name = xxx
End If
Next sht
End Sub
[/vba]

[mdmackillop]

With regard to sheets 1-15 and 16-20, you need to be more specific. Are these Sheet Names? If 10-15 don't exist, what are 16-20?

[JimS]

I import entire sheets from another source. They come in as Sheet1, Sheet1(2), Sheet1(3), Sheet1(4), etc up to 15. The existing sheets will be named something other then sheet (ie: TableData1, Summary, etc).

[JimS]

p45cal - your code works perfectly, Thanks...