Excel Format WorkSheet Tabs to "dd mmm yy" - Replacing Names - Macros Built

[truchiller]

I recently built two macros through the help of friends and google to take and build a Table of Contents - based off of an unlimited number of worksheets within a workbook. A second Macro was built to replace the information from the TOC which inputs in Column A with new information put into Column B - however the information I am inputting into Column B to rename the worksheet tabs are dates - currently formatted as dd mmm yy. When I run the macro it does not return the dates - it will only return a value if I change the format of the column to general - which places to Unicode of the date into the worksheet Tab... I am either missing a formatting string in my macro or question if I have to build another macro to translate the Unicode string into a "dd mmm yy" format. Any assistance would be great. I am creating a new schedule to where the end user only needs to change one date from year to year and all values will update after both macro's are run.

Here are the two macro's built -

Sub CreateTOC()
Dim NumSheets As Integer
'creates Table Of Contents
NumSheets = Sheets.Count
For j = 1 To NumSheets
Worksheets("TOC").Cells(j, 1) = Sheets(j).Name
Next j
End Sub
----------------------------------------------------------------------
Sub ChangeSheetNames()
Dim ws As Worksheet
Dim r As Range
Dim s As Range
Set r = Worksheets("TOC").Range("A1", Range("A65536").End(xlUp))
For Each s In r.Cells
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "TOC" Then
If ws.Name = s Then
ws.Name = s.Offset(0, 1)
On Error Resume Next 'Will continue if an error results
End If
End If
Next ws
Next s
End Sub


Column A (TOC) = the data extracted from the TOC Macro
Column B (TOC) = the new worksheet tab name assignments currently equal to values set in Column C
Column C (TOC)= dates and formulas - currently C2 equals 3 Jan 10 and C3 equals (C2+7) and so on throughout the column.

Col A Col B Col C
<TABLE cellSpacing=0 cellPadding=1 border=1><TBODY><TR><TD>TOC</TD><TD>Macro Ref</TD><TD colSpan=2>Change Formula</TD></TR><TR><TD>40181</TD><TD>03 Jan 10</TD><TD>03 Jan 10</TD><TD>Change this entry only</TD></TR><TR><TD>40188</TD><TD>10 Jan 10</TD><TD>10 Jan 10</TD></TR><TR><TD>40195</TD><TD>17 Jan 10</TD><TD>17 Jan 10</TD></TR><TR><TD>40202</TD><TD>24 Jan 10</TD><TD>24 Jan 10</TD></TR><TR><TD>40209</TD><TD>31 Jan 10</TD><TD>31 Jan 10</TD></TR><TR><TD>40216</TD><TD>07 Feb 10</TD><TD>07 Feb 10</TD></TR><TR><TD>40223</TD><TD>14 Feb 10</TD><TD>14 Feb 10</TD></TR><TR><TD>40230</TD><TD>21 Feb 10</TD><TD>21 Feb 10</TD></TR><TR><TD>40237</TD><TD>28 Feb 10</TD><TD>28 Feb 10</TD></TR></TBODY></TABLE>

thanks for anyones help in resolving my missing link...
- J

 

[xenou]

Use the format function to format your input (the dates in column 2 or what have). This returns a formatted date string value.

ws.Name = Format(s.Offset(0, 1), "dd mmm yy")

Is that it?

 

[truchiller]

Awesome - thanks for your help Xenou... I originally tried something similar but had the syntex order all messed up - I actually tried to format it after the Offset fuction versus including the format in the offset fuction... Thanks for much for your help! Works like a charm... quick and easy fix.

 

[xenou]

my pleasure,
ξ

 

[truchiller]

Xenou,

I was wondering if you could assist one more time. In order for me to re-run the TOC Macro on a later year lets say 2011, the current Macro does not recogize the format of the Worksheet Tab when renamed as "dd mmm yy." Can you assist on how to fix that Macro so I can continually update the workbook with when new dates have been updated?

Currently the issue is once it renames the worksheet tabs to "dd mmm yy" - it translates that info into the TOC - however when I go to rename the Column B with an update - it cannot put the TOC information with the current worksheet tabs... so it does not update. It is like the Macro is a one time deal. Thanks.

TOC Macro:
Sub CreateTOC()
Dim NumSheets As Integer
'creates Table Of Contents
NumSheets = Sheets.Count
For j = 1 To NumSheets
Worksheets("TOC").Cells(j, 1) = Sheets(j).Name
Next j
End Sub

ChangeSheets Macro:
Sub ChangeSheetNames()
Dim ws As Worksheet
Dim r As Range
Dim s As Range
Set r = Worksheets("TOC").Range("A1", Range("A65536").End(xlUp))
For Each s In r.Cells
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "TOC" Then
If ws.Name = s Then
ws.Name = Format(s.Offset(0, 1), "dd mmm yy")
On Error Resume Next 'Will continue if an error results
End If
End If
Next ws
Next s
End Sub