Rename Worksheet to Cell Value

[Shiseiji]

Bad day. Using the information here: http://www.mrexcel.com/forum/excel-...pplications-make-sheet-name-=-cell-value.html
I had a macro doing what I wanted. Now it's not and for the life of me I can't see what I did that made it stop working. Not that I know much more than how to copy/paste & hack at code till it does what I want . . .

Error is on: ActiveSheet.Name = ActiveSheet.Range("A1")

A2 = 11/21/2016 8:25:43 AM
A1 is null

Public Sub BINameSheet_t1()
    ' Formats cell A2 in the 5 line header as a short date.
    ' Cell A1 renders cell A2 as text.
    ' Names the worksheet the date of the report.
    Range("A2").NumberFormat = "m-dd-yy"
    Range("A1").FormulaR1C1 = "=TEXT(R[1]C,""m-dd-yy"")"
    ActiveSheet.Name = ActiveSheet.Range("A1")
End Sub

Result:

Run-time error '1004':

You typed an invalid name for a sheet or chart. Make sure that:
• The name that you type does not exceed 31 characters.
• The name does not contain any of the following characters: : \ / ? * [ or ]
• You did not leave the name blank.

TIA

Ron

 

[Greg Truby]

It A1 is null, then yeah, you're gonna have problems.

 

[Greg Truby]

Oh, and FWIW, you needn't mess around with cell A1 at all. If A2 has the value and the format you want then you can just do:

ActiveSheet.Name = Range("A2").Text

A range object's .Text property will return what you see in the cell (i.e. the number with its formatting applied).

 

[Shiseiji]

Thank you!! I got ahead of myself and didn't remark out properly what "was" working. Must have been getting the text into cell A1. The FWIW is, I think, worth more than anything else that helps me get my head around this as much as I can!

And better yet, helped me simplify combining another piece of code I'm putting into my routine.

LastRow As Long, RangeName1 As String, RangeName2 As String
    LastRow = Cells(Cells.Rows.Count, "D").End(xlUp).Row
    RangeName1 = "Over6_"
    RangeName2 = ActiveSheet.Name
    Range("A2").NumberFormat = "m-dd-yy"
    ActiveSheet.Name = Range("A2").Text
    Range("C" & LastRow).Value = RangeName1 & RangeName2
End Sub

Now I have to retrieve that named range onto my summary tab. But that's another topic question if I can't figure it out . . .

Again, the half-blind man thanks you!

Ron