Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
"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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how can i open multiple excel sheets separately | Excel Discussion (Misc queries) | |||
Copying multiple sheets from one book 2 another and undertake spec | Excel Discussion (Misc queries) | |||
Multiple sheets selected | Excel Discussion (Misc queries) | |||
linking multiple sheets to a summary sheet | Excel Discussion (Misc queries) | |||
How do I unhide multiple sheets? | Excel Worksheet Functions |