Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Mark T
 
Posts: n/a
Default 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.
  #2   Report Post  
Bob Phillips
 
Posts: n/a
Default

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.



  #3   Report Post  
moi
 
Posts: n/a
Default

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.



  #4   Report Post  
Max
 
Posts: n/a
Default

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.





  #5   Report Post  
Mangesh Yadav
 
Posts: n/a
Default

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.









  #6   Report Post  
Max
 
Posts: n/a
Default

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


  #7   Report Post  
Bob Phillips
 
Posts: n/a
Default

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.







  #8   Report Post  
Mangesh Yadav
 
Posts: n/a
Default

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.







  #9   Report Post  
Max
 
Posts: n/a
Default

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



  #10   Report Post  
Mangesh Yadav
 
Posts: n/a
Default

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







  #11   Report Post  
Max
 
Posts: n/a
Default

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


  #12   Report Post  
Bob Phillips
 
Posts: n/a
Default

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




  #13   Report Post  
Bob Phillips
 
Posts: n/a
Default

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






  #14   Report Post  
Max
 
Posts: n/a
Default

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


  #15   Report Post  
Max
 
Posts: n/a
Default

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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how can i open multiple excel sheets separately srinu Excel Discussion (Misc queries) 1 July 8th 05 04:38 PM
Copying multiple sheets from one book 2 another and undertake spec Pank Mehta Excel Discussion (Misc queries) 14 March 16th 05 05:41 PM
Multiple sheets selected twa14 Excel Discussion (Misc queries) 2 December 21st 04 12:15 PM
linking multiple sheets to a summary sheet greg g Excel Discussion (Misc queries) 1 December 16th 04 08:43 AM
How do I unhide multiple sheets? ahonig Excel Worksheet Functions 3 December 14th 04 10:03 PM


All times are GMT +1. The time now is 03:16 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"