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