Excel Worksheet Tabs - Format to "dd mmm yy" Macros Built - Missing Format

What is the purpose of building the table of contents?

There is a method you could use in your first sub that could make those items into active links to jump to the clicked-on item.

I think you are saying you wish for the tab names to contain the dates? and the table of contents to link to them. Where is the date information coming from for the names? Here is a method of creating hyperlinks in excel. Yes you can use hyperlinks to select the other sheets.. its better because they are underlined.

Date information is stored as that number you are finding, but there is an algorithm for converting it to a desired format. Here is a description of the problem.. and potential solution. thought there was a function for it already.. but couldn't find it.. will keep seaching.

Chris

Chris,

I am using the TOC essentially as a way of running the Macro's to rename the Sheet Tabs into a list of dates I put into the table. I want to the Macro to create me a list in a TOC in Column A - then take the values in Column A and replace them with the values in Column B - this way I can update a single cell within the TOC created page - and run the Macro to update the calender from year to year or from a date range to a date range...

I have the formatting issue resolved now in the ChangeSheetName () Macro to accept the value of a date in the "dd mmm yy" format. However, the issue I am having now is getting the TOC () Macro to understand that format when it creates a new TOC and then assigned the newly updated values in Column B into the worksheet - it currently will not replace the value in Column A in a Cell Formatted "dd mmm yy" value with the new value in Column B. Hope this makes sense.

Eventually I will link the TOC to the sheet tabs to ease the usage for the end user.

Thanks.

-J

While renaming the sheet try using .text

ws.Name = s.Offset(0, 1).text instead of ws.Name = s.Offset(0,1)

Please let me know if it works.

The s.Offset(0,1).text addition solved the problem of the format - however I now have an issue in the first Macro with it understanding the format of the newly assigned tabs.

If I re-run Macro: CreateTOC() - it does not pick up the new name of the worksheet tab - another formatting issue somewhere... I tried to use Format(" ", "dd mmm yy") in the programming but it still does not recognize the format... Can you assist.

My big goal is to continue to use this sheet from here on out... changing one cell over to a new date - which updates all worksheets - and executing two Macros to change the names of the Worksheet Tabs with the newly updated information.

I have relisted the Macro below for the TOC () and the ChangeSheetsName()

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).Text
On Error Resume Next 'Will continue if an error results
End If
End If
Next ws
Next s
End Sub

Thanks for your help.

I couldn't really understand the issue you mentioned in your reply.

I tried your code. during the first run, all seems to be normal, but when you run it a second time to collect the sheet names you already created with the macro, it collects the dates but inserts a dash (-) between the numbers (dd-mmm-yy), but you don't want that (if I'm not mistaking you want dd mmm yy) so when you try it again it is unable to find the sheets with the names corresponding to column A to replace them.

I solved this issue by specifying, in Excel and not programmatically, that the format of Column A is "text" instead of general and now it works well.

I hope that was your problem, if not, please provide more detailed descriptions, with the error you are getting (if any) so I can fully understand what is happening...

Personal advice: add some code in the CreateTOC macro to clear the content of column A if the user is allowed to delete sheets, otherwise you'll end up with unwanted names of none existing sheets in Column A.

You can use the range(x,y).ClearContents.

Regards,

Regards,

GA

Good Call on clearing out the TOC Column A before updating the workbook again if a date or a sheet changes. Everything works great - I thought I tired the text format but guess I had it on general - which did not recognize it through the Macro.

Formatting the column A as Text resolved the situation.

Thanks for your help.