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.

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.
> >
> >
>
>

"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
----

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.
> >
> >
>
>

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
>
>

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.
> >
> >
>
>

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
> ----
>
>

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.