Renaming multiple sheets

[Guest]

If I have 100+ sheets with with data for a single day on each sheet. The date
of data located in cell A2. Is there a quick way to rename all the sheets to
match what is in cell A2 of each sheet.

 

[Bob Phillips]

With VBA

For Each sh In ACtiveworkbbok.Worksheets
sh.Name = sh.Range("A2").Value
Next sh

 

[moi]

Dim wb As Workbook
Dim ws As Worksheet
Set wb = ThisWorkbook
For Each ws In ThisWorkbook.Worksheets
ws.Name = ws.Cells(2, 1).Value
Next

 

[Max]

Hi Bob,

When I tested with *dates* in A2 (as per OP), hit the error at this line

sh.Name = sh.Range("A2").Value

Maybe due to an invalid character: "/" arising from the date for the
sheetname

How to modify to get over this ?
Thanks

 

[Mangesh Yadav]

Hi Max,

"/" is not allowed, so maybe you could format the date as mm-dd-yy or
dd-mm-yy

Mangesh

 

[Max]

"/" is not allowed, so maybe you could format the date as mm-dd-yy or
dd-mm-yy

Yes, tried that earlier, but didn't work. Same error hit.
Think the underlying value in A2 still retains the "/"
What more can we do ?

 

[Bob Phillips]

One way Max is to replace them

Dim sh
For Each sh In ActiveWorkbook.Worksheets
sh.Name = Replace(sh.Range("A2").Value, "/", "-")
Next sh


another ius to format dates

Dim sh
For Each sh In ActiveWorkbook.Worksheets
If IsDate(sh.Range("A2").Value) Then
sh.Name = Format(sh.Name, "yyyy-mmm-dd")
Else
sh.Name = sh.Range("A2").Value
End If
Next sh

Really we need a generic RgExp to replace all offending characters.

 

[Mangesh Yadav]

something like:

For Each sh In ActiveWorkbook.Worksheets

If IsDate(sh.Range("A2")) Then
sh.Name = Format(sh.Range("A2").Value, "dd-mm-yy")
Else
sh.Name = sh.Range("A2").Value
End If

Next sh


Mangesh

 

[Max]

Yes ! That did it.
Thanks, Mangesh !

 

[Mangesh Yadav]

You're welcome.

Mangesh

Yes ! That did it.
Thanks, Mangesh !

 

[Max]

Thanks for the suggestions, Bob !

Tried:

Dim sh
For Each sh In ActiveWorkbook.Worksheets
sh.Name = Replace(sh.Range("A2").Value, "/", "-")
Next sh

but hit a compile error: Sub or Function not defined:
"Replace" was highlighted in this line

sh.Name = Replace(sh.Range("A2").Value, "/", "-")

Think maybe my Excel 97 just doesn't have this function ..

As for your 2nd suggestion, I got it to work after amending slightly the
line:

sh.Name = Format(sh.Name, "yyyy-mmm-dd")
to:
sh.Name = Format(sh.Range("A2"), "yyyy-mmm-dd")

(Amended after comparing Mangesh's code - which worked earlier - with yours)

 

[Bob Phillips]

Sorry Max,

Missed the XL97 at the foot. About time you entered the 21st century young
man .

Add this to your code

#If VBA6 Then
#Else
Function Replacex(expression As String, find_string As String, replacement
As String)
Dim i As Long
Dim iLen As Long
Dim iNewLen As Long
Dim sTemp As String

sTemp = expression
iNewLen = Len(find_string)
For i = 1 To Len(sTemp)
iLen = Len(sTemp)
If Mid(sTemp, i, iNewLen) = find_string Then
sTemp = Left(sTemp, i - 1) & replacement & Right(sTemp, iLen -
i - iNewLen + 1)
i = i + iNewLen - 1
End If
Next i

Replacex = sTemp

End Function
#End If

This will still work then when you upgrade.

 

[Bob Phillips]

Oops I put an x in the name to test in on 2000. Try this version

#If VBA6 Then
#Else
Function Replace(expression As String, find_string As String, replacement As
String)
Dim i As Long
Dim iLen As Long
Dim iNewLen As Long
Dim sTemp As String

sTemp = expression
iNewLen = Len(find_string)
For i = 1 To Len(sTemp)
iLen = Len(sTemp)
If Mid(sTemp, i, iNewLen) = find_string Then
sTemp = Left(sTemp, i - 1) & replacement & Right(sTemp, iLen -
i - iNewLen + 1)
i = i + iNewLen - 1
End If
Next i

Replace = sTemp

End Function
#End If

 

[Max]

Many thanks for the Replace function, Bob !
Tested - the first sub now runs smoothly w/o so much as a burp <bg>
Cheers

 

[Max]

Missed the XL97 at the foot. About time you entered

the 21st century young man .

No choice, Bob. 97's the only version I've got. Which also happens to be
the baseline version that's running here all over the workplace. Then
again, who knows, maybe one fine day, some kind soul somewhere might just

.. young man ..

Thanks! I'm almost half a century young <g> Your goodself ??