Rename active sheet
 

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

Rename active sheet
 

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

Rename active sheet
 

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

Rename active sheet
 

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

Rename active sheet
 

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

Rename active sheet
 

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

Rename active sheet
 

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

Rename active sheet
 

Compactness!! Now down to 1 lines :)


Sub Rename()
Activesheet.Name = ActiveSheet.Cells(Activesheet.Rows.Count, "A").End(xlUp)
& Format(Activesheet.Cells(Activesheet.Rows.Count, "E").End(xlUp), "m-d-yy
h-mmAM/PM")
End Sub
--

Regards,
Nigel




"SteveM" wrote in message
...
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 :)

Rename active sheet
 

I like one-liners more than most (I'm sort of "famous" for them over in the
compiled VB newsgroups), but there is too much repeating in your one-liner
for my tastes (in addition to the fact that your one-liner requires one to
type in some 26 characters more than the three-liner I posted).

Rick


"Nigel" wrote in message
...
Compactness!! Now down to 1 lines :)


Sub Rename()
Activesheet.Name = ActiveSheet.Cells(Activesheet.Rows.Count,
"A").End(xlUp) & Format(Activesheet.Cells(Activesheet.Rows.Count,
"E").End(xlUp), "m-d-yy h-mmAM/PM")
End Sub
--

Regards,
Nigel




"SteveM" wrote in message
...
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 :)

Rename active sheet
 

Darn it must try harder LOL

--

Regards,
Nigel




"Rick Rothstein (MVP - VB)" wrote in
message ...
I like one-liners more than most (I'm sort of "famous" for them over in the
compiled VB newsgroups), but there is too much repeating in your one-liner
for my tastes (in addition to the fact that your one-liner requires one to
type in some 26 characters more than the three-liner I posted).

Rick


"Nigel" wrote in message
...
Compactness!! Now down to 1 lines :)


Sub Rename()
Activesheet.Name = ActiveSheet.Cells(Activesheet.Rows.Count,
"A").End(xlUp) & Format(Activesheet.Cells(Activesheet.Rows.Count,
"E").End(xlUp), "m-d-yy h-mmAM/PM")
End Sub
--

Regards,
Nigel




"SteveM" wrote in message
...
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 :)

Rename active sheet
 

My original post was as follows (modified below to use the Format function),
the version without the use of With was to get it on one line, but as Rick
pointed out that was longer in character count than his three liner, well
the following is only 127 characters and maybe is the shortest code
possible??

With ActiveSheet
.Name = .Cells(.Rows.Count, 1).End(xlUp) & _
Format(.Cells(.Rows.Count, 5).End(xlUp),"m-d-yy h-mmAM/PM")
End With

Sorry Rick but this will not run on you TI 99/4 or a Sinclair Z81 !!

Cheers


--

Regards,
Nigel




"Chip Pearson" wrote in message
...
Take a peek at the With statement. It can streamline many functions.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

"Nigel" wrote in message
...
Darn it must try harder LOL

--

Regards,
Nigel




"Rick Rothstein (MVP - VB)" wrote in
message ...
I like one-liners more than most (I'm sort of "famous" for them over in
the compiled VB newsgroups), but there is too much repeating in your
one-liner for my tastes (in addition to the fact that your one-liner
requires one to type in some 26 characters more than the three-liner I
posted).

Rick


"Nigel" wrote in message
...
Compactness!! Now down to 1 lines :)


Sub Rename()
Activesheet.Name = ActiveSheet.Cells(Activesheet.Rows.Count,
"A").End(xlUp) & Format(Activesheet.Cells(Activesheet.Rows.Count,
"E").End(xlUp), "m-d-yy h-mmAM/PM")
End Sub
--

Regards,
Nigel




"SteveM" wrote in message
...
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 :)

Rename active sheet
 

My original post was as follows (modified below to use the Format
function), the version without the use of With was to get it on one line,
but as Rick pointed out that was longer in character count than his three
liner, well the following is only 127 characters and maybe is the shortest
code possible??

With ActiveSheet
.Name = .Cells(.Rows.Count, 1).End(xlUp) & _
Format(.Cells(.Rows.Count, 5).End(xlUp),"m-d-yy h-mmAM/PM")
End With

It is not shorter than my code (as long as you squeeze out the blank spaces
that you don't have to type in because VBA will inserts them automatically).
I also added the separating space between concatenation of the the two cell
contents to your code that the OP indicated she wanted (which I did by
simply adding a space to the beginning of the pattern string in the Format
function call). When these both have been done, your code counts 5
characters longer than mine... and that is with my leaving in the .Value
property call in my code (you left it out of your code because it is the
default property for your Cells object "chain" in the first part of your
code). I personally do not like relying on default object properties in
code; but if I remove that from my code, your code is 11 characters longer
than mine.

Sorry Rick but this will not run on you TI 99/4 or a Sinclair Z81 !!

LOL... no, almost nothing we write today could run on them. By the way, one
of the computers I had across the years (and there were many) was a Timex
computer which was the US version of the Sinclair, although I came by it
rather late... my mother when to a time-share meeting of some sort and the
Timex was one of the give-away inducement gifts the sponsors gave for
attending... my mother got it for my son who was too young to use it at the
time and, by the time he grew up enough to make use of a computer, that one
was long out-of-favor (my mother was not too up on technology in those
days)... I still have that computer (in the original box) up in a closet
somewhere.

Rick

Rename active sheet
 

OK I surrender!

My first ZX81 was in kit form and had to be assembled, bit of soldering and
an hour or two of fiddling. It was 'far' superior to the ZX80 at the time
(but cannot remember why!) maybe it was the 1kB of memory!

ZX80 sell for over £200 on eBay, ZX81 merely ~£50

Have a great day!

--

Regards,
Nigel




"Rick Rothstein (MVP - VB)" wrote in
message ...
My original post was as follows (modified below to use the Format
function), the version without the use of With was to get it on one line,
but as Rick pointed out that was longer in character count than his three
liner, well the following is only 127 characters and maybe is the
shortest code possible??

With ActiveSheet
.Name = .Cells(.Rows.Count, 1).End(xlUp) & _
Format(.Cells(.Rows.Count, 5).End(xlUp),"m-d-yy h-mmAM/PM")
End With

It is not shorter than my code (as long as you squeeze out the blank
spaces that you don't have to type in because VBA will inserts them
automatically). I also added the separating space between concatenation of
the the two cell contents to your code that the OP indicated she wanted
(which I did by simply adding a space to the beginning of the pattern
string in the Format function call). When these both have been done, your
code counts 5 characters longer than mine... and that is with my leaving
in the .Value property call in my code (you left it out of your code
because it is the default property for your Cells object "chain" in the
first part of your code). I personally do not like relying on default
object properties in code; but if I remove that from my code, your code is
11 characters longer than mine.

Sorry Rick but this will not run on you TI 99/4 or a Sinclair Z81 !!

LOL... no, almost nothing we write today could run on them. By the way,
one of the computers I had across the years (and there were many) was a
Timex computer which was the US version of the Sinclair, although I came
by it rather late... my mother when to a time-share meeting of some sort
and the Timex was one of the give-away inducement gifts the sponsors gave
for attending... my mother got it for my son who was too young to use it
at the time and, by the time he grew up enough to make use of a computer,
that one was long out-of-favor (my mother was not too up on technology in
those days)... I still have that computer (in the original box) up in a
closet somewhere.

Rick

Rename active sheet
 

My first ZX81 was in kit form and had to be assembled, bit of soldering
and an hour or two of fiddling. It was 'far' superior to the ZX80 at the
time (but cannot remember why!) maybe it was the 1kB of memory!

I didn't realize they came in "build-it-yourself" kit form (the Timex
version I have came all put together).

ZX80 sell for over £200 on eBay, ZX81 merely ~£50

Those are what it is selling for... in today's market? Really? Hmm! I wonder
if my Timex one is worth that kind of money? I'll have to check it out.

Rick

Rename active sheet
 

My first ZX81 was in kit form and had to be assembled, bit of soldering
and an hour or two of fiddling. It was 'far' superior to the ZX80 at the
time (but cannot remember why!) maybe it was the 1kB of memory!

I didn't realize they came in "build-it-yourself" kit form (the Timex
version I have came all put together).

ZX80 sell for over £200 on eBay, ZX81 merely ~£50

Those are what it is selling for... in today's market? Really? Hmm! I
wonder if my Timex one is worth that kind of money? I'll have to check it
out.

I just dug my Timex computer out of the closet (man, but it collected a lot
of dust after all these years)... the box calls it a Timex Sinclair 1000...
not sure how that relates to the ZX numbering system.

Rick

Rename active sheet
 

Apparently most of the ZX81 were sold ready made, the TS1000 is a re-badged
ZX81 for the US Market.

--

Regards,
Nigel




"Rick Rothstein (MVP - VB)" wrote in
message ...
My first ZX81 was in kit form and had to be assembled, bit of soldering
and an hour or two of fiddling. It was 'far' superior to the ZX80 at
the time (but cannot remember why!) maybe it was the 1kB of memory!

I didn't realize they came in "build-it-yourself" kit form (the Timex
version I have came all put together).

ZX80 sell for over £200 on eBay, ZX81 merely ~£50

Those are what it is selling for... in today's market? Really? Hmm! I
wonder if my Timex one is worth that kind of money? I'll have to check it
out.

I just dug my Timex computer out of the closet (man, but it collected a
lot of dust after all these years)... the box calls it a Timex Sinclair
1000... not sure how that relates to the ZX numbering system.

Rick

Rename active sheet
 

ZX80 sell for over £200 on eBay, ZX81 merely ~£50

Those are what it is selling for... in today's market? Really?
Hmm! I wonder if my Timex one is worth that kind of money?

Apparently most of the ZX81 were sold ready made, the TS1000
is a re-badged ZX81 for the US Market.

A re-badged ZX81 you say... Damn! That means my TS1000 isn't worth that much.

Rick