PDA

View Full Version : Solved: Macro with a Formula to rename sheets



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

mdmackillop
07-10-2009, 10:44 AM
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
07-10-2009, 11:09 AM
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 (file://\\PCS004.USA.01.West1) were in cell B1 then PCS004 would be the new Sheet Name

JimS
07-10-2009, 12:26 PM
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
07-10-2009, 01:15 PM
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: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

mdmackillop
07-10-2009, 02:28 PM
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
07-10-2009, 04:47 PM
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
07-10-2009, 06:26 PM
p45cal - your code works perfectly, Thanks...