Rename Worksheet to Cell Value

Shiseiji

Board Regular
Joined
Oct 23, 2009
Messages
132
Bad day. Using the information here: http://www.mrexcel.com/forum/excel-questions/849243-visual-basic-applications-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

Code:
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

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,013
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

Board Regular
Joined
Oct 23, 2009
Messages
132
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.

Code:
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
 

Forum statistics

Threads
1,085,726
Messages
5,385,537
Members
401,958
Latest member
rsfalkowski

Some videos you may like

This Week's Hot Topics

Top