I'm looking for a macro that will take the last used cell with a value
in column A and E. Combine the two values and rename the active sheet.
Colum A is formatted for text. Colum E is formatted for the date and
time.( m-d-yy h-mmAM/PM;@")

Example:

A E
Cedarhurst 8-3-07 9-00AM

Should rename the active sheet (Cedarhurst 8-3-07 9-00AM)



Thanks

Try this. It's not very graceful, but it works.
Private Sub RenameWS()
Dim sTemp1 As String
Dim sTemp2 As String

With ActiveSheet
Range("A" & CStr(.Rows.Count)).Select
Selection.End(xlUp).Select
sTemp1 = Selection.Text
Range("E" & CStr(.Rows.Count)).Select
Selection.End(xlUp).Select
sTemp2 = Selection.Text
' Can't have the following characters : / \ ? * [ or ]
' Just in case there are slashes in the date
sTemp2 = Replace$(sTemp2, "/", "-")
' Can't have a colon either so replace it with a dot or something else
sTemp2 = Replace$(sTemp2, ":", ".")
.Name = sTemp1 & " " & sTemp2
End With
End Sub


"Little Penny" wrote:

I'm looking for a macro that will take the last used cell with a value
in column A and E. Combine the two values and rename the active sheet.
Colum A is formatted for text. Colum E is formatted for the date and
time.( m-d-yy h-mmAM/PM;@")

Example:

A E
Cedarhurst 8-3-07 9-00AM

Should rename the active sheet (Cedarhurst 8-3-07 9-00AM)



Thanks

On Jan 6, 12:57 am, TWR wrote:
Try this. It's not very graceful, but it works.
Private Sub RenameWS()
Dim sTemp1 As String
Dim sTemp2 As String

With ActiveSheet
Range("A" & CStr(.Rows.Count)).Select
Selection.End(xlUp).Select
sTemp1 = Selection.Text
Range("E" & CStr(.Rows.Count)).Select
Selection.End(xlUp).Select
sTemp2 = Selection.Text
' Can't have the following characters : / \ ? * [ or ]
' Just in case there are slashes in the date
sTemp2 = Replace$(sTemp2, "/", "-")
' Can't have a colon either so replace it with a dot or something else
sTemp2 = Replace$(sTemp2, ":", ".")
.Name = sTemp1 & " " & sTemp2
End With
End Sub

"Little Penny" wrote:
I'm looking for a macro that will take the last used cell with a value
in column A and E. Combine the two values and rename the active sheet.
Colum A is formatted for text. Colum E is formatted for the date and
time.( m-d-yy h-mmAM/PM;@")

Example:

A E
Cedarhurst 8-3-07 9-00AM

Should rename the active sheet (Cedarhurst 8-3-07 9-00AM)

Thanks

Here's something more compact:

Sub SheetRename()
Dim aPart As String, ePart As String, shtName As String

Range("A1").EntireColumn.Cells(Rows.Count, 1).Select
Selection.End(xlUp).Select
aPart = Selection
ePart = Selection.Offset(0, 4)
shtName = aPart & " " & Format(ePart, "m-d-yy h-mmAM/PM")
ActiveSheet.Name = shtName

End Sub


SteveM

Here's something more compact:

Sub SheetRename()
Dim aPart As String, ePart As String, shtName As String

Range("A1").EntireColumn.Cells(Rows.Count, 1).Select
Selection.End(xlUp).Select
aPart = Selection
ePart = Selection.Offset(0, 4)
shtName = aPart & " " & Format(ePart, "m-d-yy h-mmAM/PM")
ActiveSheet.Name = shtName

End Sub

If compact is your goal, what about this for the body of your SheetRename
subroutine?

Dim R As Range
Set R = ActiveSheet.Cells(Rows.Count, 1).End(xlUp)
R.Parent.Name = R.Value & Format(R.Offset(0, 4).Value, " m-d-yy h-mmAM/PM")

By the way, I used R.Parent.Name to keep the last statement from
word-wrapping in newsreaders... it should be replaced by ActiveSheet.Name
for clarity.

Rick

Doesn't this assume that the last row in A and E are the same? The OP asked
for "last used cell with a value
in column A and E", which I read as - could be different?

See my solution that takes both value separately, however I should have used
the format function which would have been better!

--

Regards,
Nigel




"Rick Rothstein (MVP - VB)" wrote in
message ...
Here's something more compact:

Sub SheetRename()
Dim aPart As String, ePart As String, shtName As String

Range("A1").EntireColumn.Cells(Rows.Count, 1).Select
Selection.End(xlUp).Select
aPart = Selection
ePart = Selection.Offset(0, 4)
shtName = aPart & " " & Format(ePart, "m-d-yy h-mmAM/PM")
ActiveSheet.Name = shtName

End Sub

If compact is your goal, what about this for the body of your SheetRename
subroutine?

Dim R As Range
Set R = ActiveSheet.Cells(Rows.Count, 1).End(xlUp)
R.Parent.Name = R.Value & Format(R.Offset(0, 4).Value, " m-d-yy
h-mmAM/PM")

By the way, I used R.Parent.Name to keep the last statement from
word-wrapping in newsreaders... it should be replaced by ActiveSheet.Name
for clarity.

Rick

On Jan 6, 6:49 am, "Nigel" wrote:
Doesn't this assume that the last row in A and E are the same? The OP asked
for "last used cell with a value
in column A and E", which I read as - could be different?

See my solution that takes both value separately, however I should have used
the format function which would have been better!

--

Regards,
Nigel


"Rick Rothstein (MVP - VB)" wrote in
. ..

Here's something more compact:

Sub SheetRename()
Dim aPart As String, ePart As String, shtName As String

Range("A1").EntireColumn.Cells(Rows.Count, 1).Select
Selection.End(xlUp).Select
aPart = Selection
ePart = Selection.Offset(0, 4)
shtName = aPart & " " & Format(ePart, "m-d-yy h-mmAM/PM")
ActiveSheet.Name= shtName

End Sub

If compact is your goal, what about this for the body of your SheetRename
subroutine?

Dim R As Range
Set R = ActiveSheet.Cells(Rows.Count, 1).End(xlUp)
R.Parent.Name= R.Value & Format(R.Offset(0, 4).Value, " m-d-yy
h-mmAM/PM")

By the way, I used R.Parent.Nameto keep the last statement from
word-wrapping in newsreaders... it should be replaced by ActiveSheet.Name
for clarity.

Rick

Attn: Rick Rothstein and his solution.

Touche' on "compactness"

SteveM

P.S. And to think that I thought that it was the "C" guys who were
like that :)

Sub Rename()
With ActiveSheet
.Name = .Cells(.Rows.Count, "A").End(xlUp) & _
Replace(Replace(.Cells(.Rows.Count, "E").End(xlUp), "/", "-"), ":", "-")
End With
End Sub


--

Regards,
Nigel




"Little Penny" wrote in message
...
I'm looking for a macro that will take the last used cell with a value
in column A and E. Combine the two values and rename the active sheet.
Colum A is formatted for text. Colum E is formatted for the date and
time.( m-d-yy h-mmAM/PM;@")

Example:

A E
Cedarhurst 8-3-07 9-00AM

Should rename the active sheet (Cedarhurst 8-3-07 9-00AM)



Thanks