convert 01/01/06 to 010106

[Smithgall]

i am trying to name the sheets on file to the value in a cell. I have the code on how to change the sheet name but the value i am trying to use should be a combination of the date and the job number.

So
B2 = 09/08/06 (which is formatted to read Friday, Sep 8, 2006)
B3 = 123654 (job Number)

I want A1 to equal 090806 #123654
and then the sheet will read A1 and equal the same as A1.

As you know the sheet will not accept the dashes or any other characters. Btw i am using the following code to label the sheets


Sub Changetabname()
Dim ws As Worksheet
For Each ws In Worksheets
ws.Activate
ws.Name = Range("$A1").Value
Next
End Sub

 

[Joe4]

Try using:

ws.Name = Format(Range("$A1").Value,"mmddyy")

As long as the date in A1 is entered as a Date and not a String, this should work.

 

[Oaktree]

A1=TEXT(B2,"MMDDYY")&" #"&B3

 

[Joe4]

Sorry, I misread your post a little.

Try this in A1:

=Text(B2,"mmddyy") & "#" & B3

but, you could also do it entirely in VBA not needed to do a calc in A1 by using:

ws.Name = Format(Range("$B2"),"mmddyy") & "#" & Range("B3")