Renaming worksheets

I thought I had this working but apparently not. I have 100+ worksheets in
my workbook, I need a macro which when run will rename each worksheet to the
worksheets cell AB1 (each worksheet will have its own cell AB1 which will
contain the text to be used as the worksheets name) and when it is done it
must return me to the worksheet which was active when called the macro.

Dim WS as Worksheet
for each WS in Worksheets
WS.name = WS.Range("AB1"=.value
next WS


HTH
--
AP

"Patrick Simonds" <ordnance1@comcast.net> a écrit dans le message de
news:e4$qyu9UGHA.328@TK2MSFTNGP11.phx.gbl...
> I thought I had this working but apparently not. I have 100+ worksheets
in
> my workbook, I need a macro which when run will rename each worksheet to
the
> worksheets cell AB1 (each worksheet will have its own cell AB1 which will
> contain the text to be used as the worksheets name) and when it is done it
> must return me to the worksheet which was active when called the macro.
>
>

Hi Patrick,

Try:

'=============>>
Public Sub Tester001()
Dim SH As Worksheet

For Each SH In ActiveWorkbook.Worksheets
SH.Name = SH.Range("AB1").Value
Next SH

End Sub
'<<=============

Since no selecrions are made, the active sheet does not change,


---
Regards,
Norman



"Patrick Simonds" <ordnance1@comcast.net> wrote in message
news:e4$qyu9UGHA.328@TK2MSFTNGP11.phx.gbl...
>I thought I had this working but apparently not. I have 100+ worksheets in
>my workbook, I need a macro which when run will rename each worksheet to
>the worksheets cell AB1 (each worksheet will have its own cell AB1 which
>will contain the text to be used as the worksheets name) and when it is
>done it must return me to the worksheet which was active when called the
>macro.
>

Typo alert.

Dim WS as Worksheet
for each WS in Worksheets
WS.name = WS.Range("AB1").value
next WS

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Ardus Petus" <ardus.petus@laposte.net> wrote in message
news:%23e5Mpx9UGHA.4348@TK2MSFTNGP09.phx.gbl...
> Dim WS as Worksheet
> for each WS in Worksheets
> WS.name = WS.Range("AB1"=.value
> next WS
>
>
> HTH
> --
> AP
>
> "Patrick Simonds" <ordnance1@comcast.net> a écrit dans le message de
> news:e4$qyu9UGHA.328@TK2MSFTNGP11.phx.gbl...
> > I thought I had this working but apparently not. I have 100+ worksheets
> in
> > my workbook, I need a macro which when run will rename each worksheet to
> the
> > worksheets cell AB1 (each worksheet will have its own cell AB1 which
will
> > contain the text to be used as the worksheets name) and when it is done
it
> > must return me to the worksheet which was active when called the macro.
> >
> >
>
>

Thanks!

--
AP

"Bob Phillips" <bob.phillips@notheretiscali.co.uk> a écrit dans le message
de news:%23WKNLA%23UGHA.5172@TK2MSFTNGP12.phx.gbl...
> Typo alert.
>
> Dim WS as Worksheet
> for each WS in Worksheets
> WS.name = WS.Range("AB1").value
> next WS
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Ardus Petus" <ardus.petus@laposte.net> wrote in message
> news:%23e5Mpx9UGHA.4348@TK2MSFTNGP09.phx.gbl...
> > Dim WS as Worksheet
> > for each WS in Worksheets
> > WS.name = WS.Range("AB1"=.value
> > next WS
> >
> >
> > HTH
> > --
> > AP
> >
> > "Patrick Simonds" <ordnance1@comcast.net> a écrit dans le message de
> > news:e4$qyu9UGHA.328@TK2MSFTNGP11.phx.gbl...
> > > I thought I had this working but apparently not. I have 100+
worksheets
> > in
> > > my workbook, I need a macro which when run will rename each worksheet
to
> > the
> > > worksheets cell AB1 (each worksheet will have its own cell AB1 which
> will
> > > contain the text to be used as the worksheets name) and when it is
done
> it
> > > must return me to the worksheet which was active when called the
macro.
> > >
> > >
> >
> >
>
>

This does not seem to work. It runs through all the sheets but does not
rename them. This is what is found in cell AB1:

=TEXT($A$4,"dd mmm yy")

I did not referance cell A4 directly because it contained a formula to
arrive at the date.


"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:%23WKNLA%23UGHA.5172@TK2MSFTNGP12.phx.gbl...
> Typo alert.
>
> Dim WS as Worksheet
> for each WS in Worksheets
> WS.name = WS.Range("AB1").value
> next WS
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Ardus Petus" <ardus.petus@laposte.net> wrote in message
> news:%23e5Mpx9UGHA.4348@TK2MSFTNGP09.phx.gbl...
>> Dim WS as Worksheet
>> for each WS in Worksheets
>> WS.name = WS.Range("AB1"=.value
>> next WS
>>
>>
>> HTH
>> --
>> AP
>>
>> "Patrick Simonds" <ordnance1@comcast.net> a écrit dans le message de
>> news:e4$qyu9UGHA.328@TK2MSFTNGP11.phx.gbl...
>> > I thought I had this working but apparently not. I have 100+
>> > worksheets
>> in
>> > my workbook, I need a macro which when run will rename each worksheet
>> > to
>> the
>> > worksheets cell AB1 (each worksheet will have its own cell AB1 which
> will
>> > contain the text to be used as the worksheets name) and when it is done
> it
>> > must return me to the worksheet which was active when called the
>> > macro.
>> >
>> >
>>
>>
>
>