Der erste Buchstabe eines Satzes
soll in Großbuchstaben unmgewandelt werden.

Es wird die gesamte Spalte A durchlaufen:

Sub Erster_Buchstabe_groß()
    z = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    For Each Zelle In Worksheets("Tabelle1").Range("A1:A" & z)
        s = Zelle.Value
        a = 0
        For x = 1 To Len(s)
            If Mid(s, x, 1) = " " Then a = a + 1
        Next x
        t = ""
        For x = 1 To a + 1
            tmp = strParse(s, " ", x)
            tmp = UCase(Left(tmp, 1)) & LCase(Mid(tmp, 2, Len(tmp)))
            t = t & " " & tmp
        Next x
        Zelle.Value = Trim(t)
    Next Zelle
End Sub

Public Function strParse(ByVal strText As String, _
    ByVal Trennzeichen As String, ByVal Position As Integer) As String
    Dim posStart, posStop, lenCar As Integer
    posStart = 1
    Do While Position > 1
        posStart = InStr(posStart, strText, Trennzeichen) + 1
        Position = Position - 1
    Loop
    posStop = InStr(posStart, strText, Trennzeichen)
    strParse = Mid(strText, posStart, IIf(posStop = 0, Len(strText) + 1, _
      posStop - posStart))
End Function