Renaming multiple sheets
 

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.

With VBA

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

--
HTH

Bob Phillips

"Mark T" <Mark wrote in message
...
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.

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


"Mark T" <Mark schreef in bericht
...
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.

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
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Bob Phillips" wrote in message
...
With VBA

For Each sh In ACtiveworkbbok.Worksheets
Next sh

--
HTH

Bob Phillips

"Mark T" <Mark wrote in message
...
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.

Hi Max,

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

Mangesh



"Max" wrote in message
...
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
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Bob Phillips" wrote in message
...
With VBA

For Each sh In ACtiveworkbbok.Worksheets
Next sh

--
HTH

Bob Phillips

"Mark T" <Mark wrote in message
...
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.

"Mangesh Yadav" wrote:
"/" 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 ?
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

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.

--
HTH

Bob Phillips

"Max" wrote in message
...
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
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Bob Phillips" wrote in message
...
With VBA

For Each sh In ACtiveworkbbok.Worksheets
Next sh

--
HTH

Bob Phillips

"Mark T" <Mark wrote in message
...
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.

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" wrote in message
...
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
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Bob Phillips" wrote in message
...
With VBA

For Each sh In ACtiveworkbbok.Worksheets
Next sh

--
HTH

Bob Phillips

"Mark T" <Mark wrote in message
...
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.

Yes ! That did it.
Thanks, Mangesh !
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Mangesh Yadav" wrote in message
...
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

You're welcome.

Mangesh



"Max" wrote in message
...
Yes ! That did it.
Thanks, Mangesh !
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Mangesh Yadav" wrote in message
...
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

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)

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

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.

--
HTH

Bob Phillips

"Max" wrote in message
...
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)

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

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


--
HTH

Bob Phillips

"Bob Phillips" wrote in message
...
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.

--
HTH

Bob Phillips

"Max" wrote in message
...
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)

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Many thanks for the Replace function, Bob !
Tested - the first sub now runs smoothly w/o so much as a burp <bg
Cheers
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

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
throw me an upgrade to say, xl2000? that s/he doesn't need anymore <g.

.. young man ..
Thanks! I'm almost half a century young <g Your goodself ??
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----