Macro que nombre una hoja con el nombre de las celdas de una columna

Pon la siguiente macro en los eventos de tu hoja

Private Sub Worksheet_Change(ByVal Target As Range)
'Por.Dante Amor
    If Not Intersect(Target, Columns("A")) Is Nothing Then
        If Target.Count > 1 Then Exit Sub
        If Target.Value = "" Then Exit Sub
        existe = False
        For Each h In Sheets
            If LCase(h.Name) = LCase(Left(Target.Value, 30)) Then
                existe = True
            End If
        Next
        caracs = Array("\", "/", "?", "*", "[", "]")
        If existe Then
            MsgBox "Ya existe la hoja con ese nombre"
        Else
            For c = LBound(caracs) To UBound(caracs)
                If InStr(1, Target.Value, caracs(c)) > 0 Then
                    MsgBox "Ha escrito un nombre no válido para una hoja. Asegúrese de que:" & vbCr & vbCr & _
                           "El nombre no contenga el caracter inválido: " & caracs(c)
                    Exit Sub
                End If
            Next
            Application.ScreenUpdating = False
            activa = ActiveSheet.Name
            Sheets.Add after:=Sheets(Sheets.Count)
            ActiveSheet.Name = Target.Value
            Sheets(activa).Select
            Application.ScreenUpdating = True
            MsgBox "Nueva hoja creada: " & Target.Value
        End If
    End If
End Sub

Sigue las Instrucciones para poner la macro en los eventos de worksheet

1. Abre tu libro de excel
2. Para abrir Vba-macros y poder pegar la macro, Presiona Alt + F11
3. Del lado izquierdo dice: VBAProject, abajo dale doble click a worksheet(tu hoja)
4. En el panel del lado derecho copia la macro

.

'S aludos. Dante Amor. Recuerda valorar la respuesta. G racias

.

Avísame cualquier duda

.

Una forma, muy simple, sería usar el evento "Change" de alguna hoja:

Private Sub Worksheet_Change(ByVal Target As Range)
If Application.Intersect(Target, [A1:A30]) Is Nothing Then Exit Sub
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = Target.Value
End Sub

OJO, estoy suponiendo que tu rango para ingresar nombres y crear hojas es "A1:A30". Ah, un factor a considerar debería ser que si ingresas un nombre  ya existente o caracteres no permitidos para nombres de hojas, te dará un error, pero puedes usar un controlador de errores o, si quieres ir más allá, evitar esos caracteres o nombres repetidos pero eso ya es "tarea para la casa". Salu2

Abraham Valencia