Renaming Multiple Worksheets

Hi everyone. Got a problem and I really need some help. Basically, I have a workbook with plenty of worksheets. I would like to rename the worksheets based on some criteria.

For example, in each worksheet, column A is a column for dates. None of the worksheets have the same dates:

\1

I would like the worksheet to be named: SXFMONTHDAY.YEAR

wherein MONTH is the first 3 letters of the month , DAY is the day , and year is the last 2 digits of the year. So in this case, Sheet1 would be named: SXFJan3.06

Then, Sheet2 will be renamed based on it's date and the worksheet name format just mentioned. The rest of the worksheets are also renamed the same way.

I'm sure some VBA is needed for this but I'm really bad at it. Any suggestions or comments would be greatly appreciated. Thank you!

Hello Uberathlete,

This macro will cahnge the all the worksheets names using the convention described in your post. You show only 3 letters of the symbol being used, so the macro will use only a maximum of 3 characters for the symbol. You can run the macro using ALT+F8 to display the Macro Dialog List, and run the macro by selecting it from the list.

Sub RenameWorksheets()

  Dim strDay As String
  Dim strMonth As String
  Dim strYear As String
  Dim Symbol As String
  Dim Wks AS Worksheet

    For Each Wks In Worksheets
      With Wks
        If IsDate(.Range("A2")) Then 
          With .Range("A2")
            strMonth = Format(.Value, "mmm")
            strDay = Format(.Value, "d")
            strYear = Format(.Value "yy")
          End With

          With Range("B2")
            If Len(.Value) < 3 Then 
               Symbol = .Value
            Else
               Symbol = Left(.Value, 3)
            End If
          End With
   
          .Name  = Symbol & strMonth & strDay & "." & strYear
        End If
      End With
    Next Wks

End Sub

To Install the Macro:
1. Copy the Macro code above using CTRL+C
2. Open Excel and Right Click on any Sheet Tab
3. Click on View Code in the pop up menu
4. Use ALT+I to activate the VBE Insert Menu
5. Press the letter m to insert a Standard Module
6. Paste the macro code using CTRL+V
7. Save the macro using CTRL+S
8. Close the VBE and return to excel using ALT+Q

Sincerely,
Leith Ross

Hi there,

Here's my attempt, which assumes the "SXF" is fixed:

Sub RenameWorkSheets()

Dim intIndex As Integer
intIndex = 1

Worksheets(intIndex).Activate
   
   Do Until intIndex = Worksheets.Count
      With ActiveSheet
        .Name = "SXF" & Format(Range("A2"), "mmmd.yy")
        .Next.Select
      End With
   intIndex = intIndex + 1
   Loop

ActiveSheet.Name = "SXF" & Format(Range("A2"), "mmmd.yy")

End Sub

HTH

Robert

Hi there,
Is there any shortcut keys or something available to rename multiple worksheets except for the VB Scripts?

Originally Posted by evelynmorry

Hi there,
Is there any shortcut keys or something available to rename multiple worksheets except for the VB Scripts?

No shortcuts. Some excel add-ins can help. Such as Kutools for Excel, PUP 7,...I have tried some of them, and keep using Kutools for Excel, for it is well designed for common users and very easy to operate. A useful and neat excel application.