Assigning a time value to a Worksheet name

[ExcelRaceRatings]

Hello,

I have a workbook with around 50 different worksheets. In cell C2 on each worksheet there is a time (eg - 10.41am). A two ended question:

1) Is there a way to assign the tab name to read the time value of C2?
2) If question one is possible then is there way to sort the sheets by time?

Thank you.

 

[afonsomira]

Hello,

Whit this code your sheets's are going to sort.

Sub Sort_Active_Book()
Dim i As Integer
Dim j As Integer
Dim iAnswer As VbMsgBoxResult

   iAnswer = MsgBox("Sort Sheets in Ascending Order?" & Chr(10) _
     & "Clicking No will sort in Descending Order", _
     vbYesNoCancel + vbQuestion + vbDefaultButton1, "Sort Worksheets")
   For i = 1 To Sheets.Count
      For j = 1 To Sheets.Count - 1

         If iAnswer = vbYes Then
            If UCase$(Sheets(j).Name) > UCase$(Sheets(j + 1).Name) Then
               Sheets(j).Move After:=Sheets(j + 1)
            End If

         ElseIf iAnswer = vbNo Then
            If UCase$(Sheets(j).Name) < UCase$(Sheets(j + 1).Name) Then
               Sheets(j).Move After:=Sheets(j + 1)
            End If
         End If
      Next j
   Next i
End Sub

This code will make the name of sheet = to cell "C2" value

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("C2")) Is Nothing Then
        ActiveSheet.Name = ActiveSheet.Range("C2")
    End If
End Sub

 

[Osvaldo Palmeiro]

Hi. Try:

Sub NameAndSortSheets()
 Dim ws As Worksheet, n As Long, i As Long, j As Long
  For Each ws In ThisWorkbook.Sheets
   ws.Name = ws.[C2]
  Next ws
  n = ThisWorkbook.Sheets.Count
  For i = 1 To n - 1
   For j = i + 1 To n
    If Left(Sheets(j).Name, InStr(Sheets(j).Name, ".") - 1) * 1 < Left(Sheets(i).Name, InStr(Sheets(i).Name, ".") - 1) * 1 _
     Or Sheets(j).Name < Sheets(i).Name Then Sheets(j).Move Before:=Sheets(i)
   Next j
  Next i
End Sub