Cambiar el nombre de una hoja automáticamente

Prueba esto

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target = Range("c2") And Target <> Empty Then
ActiveSheet.Name = Target
End If
On Error GoTo 0
End Sub

Solo ponlo en el modulo vba de la hoja1, cada que cambies el nombre de la celda c2 cambiara el nombre de la hoja donde estes

si funciona pero no me funciona como realmente quiero, la celda c2 yo directamente no la modifico sino que dicha celda copia el valor de otra celda de otra hoja y cuando la modifico en la otra hoja el valor se copia en c2 pero no me cambia el nombre de la hoja, ahora si yo manualmente modifico la celda c2 entonces si me cambia el nombre de la hoja

Entonces necesitas colocar la macro en el panel thisworkbook, en el panel explorer fijate el nombre interno que tiene la hoja que quieres cambiar en mi caso se llama hoja1 el nombre interno y en la pestaña aparece prueba como nombre externo, modificando ese dato ya la macro cambiara el nombre en la hoja que quieres.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
On Error Resume Next
If Target = Hoja1.Range("c2") And Target <> Empty Then
    Hoja1.Name = Target
End If
On Error GoTo 0
End Sub

en el caso de que sean varias hojas las que quiero cambiar como debería de poner los nombres de cada una?

Solo pones los nombres internos de la hoja

hoja1.name=target

hoja2.name=target

hoja3.name=target

Etc.

ahí ya me estoy liando, con uno solo me funciona muy bien pero poniendo mas no lo consigo algo no estoy haciendo bien, ten en cuenta que son varias hojas y cada una coge el nombre de cada una de ellas no se si me explico, hoja1 lo coge de c2 de hoja1, hoja2 lo coge de c2 hoja2, etc...

¿Una pregunta el valor de c2 de cada hoja proviene del mismo origen?, es decir si tu tecleas c2 en la hoja2 este valor se copiara en la hoja1, en la hoja3, en la hoja4, en la hoja5, de ser asi tienes el inconveniente de que Excel no puede poner hojas duplicadas.

cada celda c2 tiene un valor distinto, cada c2 coge su valor de una misma hoja pero cada uno de distinta celda, al final siempre cada c2 es distinto uno de otro siempre van a tener un valor distinto

Otra cosa que también me valdría y que quizás sea más fácil es decirle a cada hoja que copie el valor directamente desde la hoja donde lo modifico, me explico: hoja1 sea renombrado con el valor de a1 de la hoja15, que hoja2 sea renombrado con el valor de a1 de la hoja 15, que hoja3 sea renombrado con el valor de a3 de la hoja 15, y así sucesivamente hasta 20 hojas que tendría que cambiar

Vamos a suponer por un momento que en la pestaña hoja2 (nombre de la pestaña) en la columna c tienes todos los valores que se van a repartir mediante fórmula en todas las hojas en su respectiva celda c2, colocas esta macro en el modulo hoja2 de VBA y ahora cada que cambie una celda de la columna C de la hoja 2, cambiara la hoja correspondiente al valor que encuentre en dicha hoja, ojo solo funciona con las hojas existentes si la hoja no existe no hará nada.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 3 Then
For Each hoja In Worksheets
    nombre = hoja.Name
    If UCase(nombre) <> "HOJA2" Then
        Sheets(nombre).Name = Sheets(nombre).Range("c2")
    End If
Next hoja
End If
End Sub

se ha producido el error 1004 en tiempo de ejecución, error defenido por la aplicación o el objeto.

esto es lo que me dice cuando modifico el valor en la hoja 2. seria mas fácil tal y como te comentaba antes??: Otra cosa que también me valdría y que quizás sea más fácil es decirle a cada hoja que copie el valor directamente desde la hoja donde lo modifico, me explico: hoja1 sea renombrado con el valor de a1 de la hoja15, que hoja2 sea renombrado con el valor de a1 de la hoja 15, que hoja3 sea renombrado con el valor de a3 de la hoja 15, y así sucesivamente hasta 20 hojas que tendría que cambiar

El problema que veo con lo que sugieres es este la macro leerá lo que tengas en la hoja2, ¿cómo sabrá a que hoja asignaras cada nombre?, por ejemplo si cambias juan por rosa, ¿la siguiente ves que cambies rosa por carlos como sabrá que hoja cambiar?, pues mira yo pruebo las macros antes de subirlas para ver que errores se pueden presentar y no me aparece tal error, si cambio el nombre de cualquiera es esas 4 celdas la macro cambia el nombre de la hoja que tenga ese valor en c2

No se si es que el fallo estará en esa línea

Solo recuerdo dos maneras de que ese error se presente

1.- Tienes en una de las hojas vacía la celda C2 por lo cual al no tener un nombre que ponerle te pondrá un error 1004

2.- Tienes un valor repetido en la columna C de la hoja2 y por lo tanto excel no te va a dejar tener hojas duplicadas e igual te va a lanzar un error 1004 mira la imagen con tus datos

prueba con esta macro, no es lo ideal y se usa en contadas excepciones le incluí on error resume next lo cual le indicara a la macro que siga a pesar del error, checa primero que todas tus hojas estén formuladas en C2

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 3 Then
    For Each hoja In Worksheets
        nombre = hoja.Name
        If UCase(nombre) <> "HOJA2" Then
            On Error Resume Next
            Sheets(nombre).Name = Sheets(nombre).Range("c2")
            On Error GoTo 0
        End If
    Next hoja
    End If
    End Sub

esta ya si me funciona probándola en un libro aparte ahora solo me queda adecuarla a lo que realmente quiero, ya te digo algo cuando lo prueba que sino es esta noche ya será mañana

que habría que modificar para que en vez de la hoja2 sea desde otra hoja ya que he cambiado el valor en la macro y aun sigue haciéndolo desde la misma hoja

[Hola

En el módulo de la hoja en donde deseas ese efecto, pega el siguiente código:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address = "$C$2" Then
    ActiveSheet.Name = Target.Value
End If
End Sub

OJO, si en la celda ingresas caracteres no permitidos en los nombres de las hojas, te dará un error. Claro, eso puede evitarse pero ya queda como tarea para ti.

Abraham Valencia

si funciona pero no me funciona como realmente quiero, la celda c2 yo directamente no la modifico sino que dicha celda copia el valor de otra celda de otra hoja y cuando la modifico en la otra hoja el valor se copia en c2 pero no me cambia el nombre de la hoja, ahora si yo manualmente modifico la celda c2 entonces si me cambia el nombre de la hoja

Joder esto es más complicado de lo que yo me creía, el problema que tengo ahora es que me cambia todas las hojas y me pone lo que hay en c2 de cada hoja y yo solo quiero que lo haga determinadas hojas