naming workbook with text from two cells

Posted by Steve on October 22, 2001 3:31 PM

I have a macro that names a sheet. Later in the macro I want to rename the sheet by combining the text from two different cells. For example, A1 has the date and A2 has a word or words. I tried

Dim name1, name2 as string
Activesheet.name = name1 & " " & name2

but it will not work. Please help me with my syntax.

Thanks,

Steve

Posted by Jonathan on October 22, 2001 4:58 PM

I think the problem is with the date. Excel won't allow a sheet name that has a / or a \ or a few other things in it. Try a non-date text in both cells and see if that gets it. It works for me. Then, as far as the rest, what error do you get if you try to use a date?

Anyway, here's my code. I have A1 and B1 with text only. A1 has the single word TODAY, and B1 has IS A GOOD DAY.

Dim name1 As String, name2 As String

name1 = ActiveSheet.Range("A1").Value
name2 = ActiveSheet.Range("B1").Value

ActiveSheet.Name = name1 & " " & name2

This renamed my sheet TODAY IS A GOOD DAY.

There is a way to get a date into a sheetname, so if it has to be that, ask again.

HTH

Posted by Steve on October 23, 2001 10:18 AM

Thanks Jonathan,
The problem lay with the cell holding the date begin formatted with mm/dd/yy. I changed the formatting to mm-dd-yy and it worked fine.

Steve

name1 = ActiveSheet.Range("A1").Value name2 = ActiveSheet.Range("B1").Value ActiveSheet.Name = name1 & " " & name2