I am trying to rename the active sheet with 1st character of firstnam
and 1st character of lastname.

E.g. in cell K2, I have the staff name John A. Doe. I want the curren
active sheet to be rename to JD (i.e. the 1st character of first an
last name respectively.

Tried doing this but it didn't work:

ActiveSheet.Name=CONCATENATE(LEFT(LEFT(K2,FIND(
",K2)-1),1),LEFT(RIGHT(K2,LEN(K2)-FIND("*",SUBSTITUTE(K2,
","*",LEN(K2)-LEN(SUBSTITUTE(K2," ",""))))),1))

Always have error in the "FIND" function. Thanks in advance for you
hel

--
Message posted from http://www.ExcelForum.com

VBA Find is not the same as the FIND() worksheet function.
To use this in VBA we have to put :-
Application.Worksheetfunction.Find(....) - each time.

Here is some simpler VBA code that also checks for a single space :-

'-------------------------------------------
Sub NAME_SHEET()
Dim MyStr As String
Dim C1 As String
Dim C2 As String
'------------------------------
MyStr = Range("K2").Value
C1 = Left(MyStr, 1)
sp1 = InStr(1, MyStr, " ", vbTextCompare) ' 1st. space
sp2 = InStr(sp1 + 1, MyStr, " ", vbTextCompare) '2nd. space
'------------------------------
If sp2 = 0 Then ' no 2nd. space
C2 = Mid(MyStr, sp1 + 1, 1)
Else
C2 = Mid(MyStr, sp2 + 1, 1)
End If
ActiveSheet.Name = C1 & C2
End Sub
'---------------------------------------------

--
Message posted from http://www.ExcelForum.com

AS long as you have XL2000 or above

Dim iPos as long
With ActiveCell
iPos = InStrRev(.Value, " ")
ActiveSheet.Name = Left(.Value, 1) & Mid(.Value, iPos + 1, 1)
End With

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"slc " wrote in message
...
I am trying to rename the active sheet with 1st character of firstname
and 1st character of lastname.

E.g. in cell K2, I have the staff name John A. Doe. I want the current
active sheet to be rename to JD (i.e. the 1st character of first and
last name respectively.

Tried doing this but it didn't work:

ActiveSheet.Name=CONCATENATE(LEFT(LEFT(K2,FIND("
",K2)-1),1),LEFT(RIGHT(K2,LEN(K2)-FIND("*",SUBSTITUTE(K2,"
","*",LEN(K2)-LEN(SUBSTITUTE(K2," ",""))))),1))

Always have error in the "FIND" function. Thanks in advance for your
help


---
Message posted from http://www.ExcelForum.com/