Rename active sheet

[Little Penny]

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

 

[TWR]

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

 

[SteveM]

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

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;@")

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

 

[Nigel]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Nigel]

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
(e-mail address removed)

 

[SteveM]

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
(e-mail address removed)

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

 

[Nigel]

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
(e-mail address removed)

 

[Rick Rothstein \(MVP - VB\)]

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]

Darn it must try harder LOL

--

Regards,
Nigel
(e-mail address removed)

 

[Rick Rothstein \(MVP - VB\)]

LOL

Rick

Darn it must try harder LOL

--

Regards,
Nigel
(e-mail address removed)

 

[Chip Pearson]

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)

 

[Rick Rothstein \(MVP - VB\)]

Attn: Rick Rothstein and his solution.

Touche' on "compactness"

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

Maybe, but my first computer was a Texas Instruments TI-99/4 purchased back
in 1981; it had a whopping 12,400 Bytes (yes, Bytes) of memory for the
programmer; and remember, this was back in the days of interpreted BASIC
where your source code shared that 12,400 Bytes with your executing code...
trust me, the knack for writing compact code becomes deeply engrained under
those restrictions.

Rick

 

[Little Penny]

Thanks Everyone............

 

[Nigel]

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
(e-mail address removed)

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Nigel]

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
(e-mail address removed)

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Rick Rothstein \(MVP - VB\)]

My first ZX81 was in kit form and had to be assembled, bit of soldering

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


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

 

[Nigel]

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

--

Regards,
Nigel
(e-mail address removed)