Home/ Questions/Q 9419
Next
Answered
computing
  • 2
Asked: 2021-12-01T12:54:29+00:00 In:

Renaming Sheet Tab With a Date From Cell

  • 2

Does anyone know how to rename the sheet name with a date? Everytime I write the macro to rename the sheet it shows the date with slashes (1/29/10) even though the date in the cell is formatted with dahses (1-29-10).

Using macro like this

ActiveSheet().Select
ActiveSheet.Name=Range(“A47”)

Share

    1 Answer

    1. Best Answer
      This code will build the Sheet Name by using the date stored in the cell.

      BTW…you don’t have to Select the ActiveSheet, it’s already “selected” since it’s Active.

      In addition, you rarely have to Select an object to perform a VBA Action on it.

      ActiveSheet.Name = “My Sheet Name”

      or

      Sheets(1).Name = “My Sheet Name”

      is more efficient.

      The same goes for cell ranges, text boxes, whatever.

      Instead of:

      Range(“A1:A100”).Select
      Selection.Interior.ColorIndex = 3

      use

      Range(“A1:A100”).Interior.ColorIndex = 3

      Option Explicit
Sub NameDateSheet()
Dim myShtName
myShtName = Month(Range("A47")) _
            & "-" & Day(Range("A47")) _
            & "-" & Year(Range("A47"))
ActiveSheet.Name = myShtName
End Sub

      • 0