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 T@discussions.microsoft.com> wrote in message
news:5101D2D5-0016-4220-B8F5-F146D0BC0D3D@microsoft.com...
> 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 T@discussions.microsoft.com> schreef in bericht
news:5101D2D5-0016-4220-B8F5-F146D0BC0D3D@microsoft.com...
> 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 <at>yahoo<dot>com
----
"Bob Phillips" <phillips@tiscali.co.uk> wrote in message
news:e1$$4PRiFHA.2560@TK2MSFTNGP10.phx.gbl...
> With VBA
>
> For Each sh In ACtiveworkbbok.Worksheets
> Next sh
>
> --
> HTH
>
> Bob Phillips
>
> "Mark T" <Mark T@discussions.microsoft.com> wrote in message
> news:5101D2D5-0016-4220-B8F5-F146D0BC0D3D@microsoft.com...
> > 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" <demechanik@yahoo.com> wrote in message
news:OQvsymRiFHA.3656@TK2MSFTNGP09.phx.gbl...
> 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 <at>yahoo<dot>com
> ----
> "Bob Phillips" <phillips@tiscali.co.uk> wrote in message
> news:e1$$4PRiFHA.2560@TK2MSFTNGP10.phx.gbl...
> > With VBA
> >
> > For Each sh In ACtiveworkbbok.Worksheets
> > Next sh
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "Mark T" <Mark T@discussions.microsoft.com> wrote in message
> > news:5101D2D5-0016-4220-B8F5-F146D0BC0D3D@microsoft.com...
> > > 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" <demechanik@yahoo.com> wrote in message
news:OQvsymRiFHA.3656@TK2MSFTNGP09.phx.gbl...
> 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 <at>yahoo<dot>com
> ----
> "Bob Phillips" <phillips@tiscali.co.uk> wrote in message
> news:e1$$4PRiFHA.2560@TK2MSFTNGP10.phx.gbl...
> > With VBA
> >
> > For Each sh In ACtiveworkbbok.Worksheets
> > Next sh
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "Mark T" <Mark T@discussions.microsoft.com> wrote in message
> > news:5101D2D5-0016-4220-B8F5-F146D0BC0D3D@microsoft.com...
> > > 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 <at>yahoo<dot>com
----

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" <demechanik@yahoo.com> wrote in message
news:OQvsymRiFHA.3656@TK2MSFTNGP09.phx.gbl...
> 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 <at>yahoo<dot>com
> ----
> "Bob Phillips" <phillips@tiscali.co.uk> wrote in message
> news:e1$$4PRiFHA.2560@TK2MSFTNGP10.phx.gbl...
> > With VBA
> >
> > For Each sh In ACtiveworkbbok.Worksheets
> > Next sh
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "Mark T" <Mark T@discussions.microsoft.com> wrote in message
> > news:5101D2D5-0016-4220-B8F5-F146D0BC0D3D@microsoft.com...
> > > 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 <at>yahoo<dot>com
----
"Mangesh Yadav" <mangesh.NOSPAMyadav@gmail.com> wrote in message
news:eVzhquRiFHA.1788@TK2MSFTNGP12.phx.gbl...
> 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" <demechanik@yahoo.com> wrote in message
news:u$fYl2RiFHA.3436@tk2msftngp13.phx.gbl...
> Yes ! That did it.
> Thanks, Mangesh !
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
> "Mangesh Yadav" <mangesh.NOSPAMyadav@gmail.com> wrote in message
> news:eVzhquRiFHA.1788@TK2MSFTNGP12.phx.gbl...
> > 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 <at>yahoo<dot>com
----

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" <demechanik@yahoo.com> wrote in message
news:OSMOQDSiFHA.708@TK2MSFTNGP09.phx.gbl...
> 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 <at>yahoo<dot>com
> ----
>
>

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" <phillips@tiscali.co.uk> wrote in message
news:%23xXi9jWiFHA.3716@TK2MSFTNGP14.phx.gbl...
> 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" <demechanik@yahoo.com> wrote in message
> news:OSMOQDSiFHA.708@TK2MSFTNGP09.phx.gbl...
> > 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 <at>yahoo<dot>com
> > ----
> >
> >
>
>

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 <at>yahoo<dot>com
----

> 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 <at>yahoo<dot>com
----