Parece que ya tendrás esa lista de hojas que estás mostrando y le querés agregar el vínculo a cada una.
Eso lo podrías resolver con unas instrucciones como las siguientes, donde recorriendo cada celda se le agregará el vínculo a su hoja.
En tu muestra no se ven los encabezados de filas y columnas (te recomiendo que los incluyas en las imágenes que dejes aquí para dejar las macros con tus referencias) .
La macro está diseñada para el ejemplo de mi imagen y se coloca en el Editor, en un módulo.
Cuando cambies de valor la celda, el vínculo se mantiene como se observa en la 2da imagen. No así cuando lo que cambia es el nombre de la hoja ya que se rompe el vínculo.
![](//blob.todoexpertos.com/uploads/md/10c7820783ecfbef46eba08280288d12.png)
Sub Incorporar_Hipervínculos()
'x Elsamatilde
'se recorre la col A de la hoja activa, a partir de la fila 2
[A2].Select
While ActiveCell <> ""
hojita = ActiveCell.Value 'el texto que será el nombre de la hoja
mensaje = "Voy a la hoja " & hojita 'el mensaje que se mmuestra al pasar el mouse por el vínculo
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:=hojita & "!A1", ScreenTip:=mensaje
ActiveCell.Offset(1, 0).Select 'pasa a la fila siguiente
Wend 'repite el bucle hasta encontrar una celda vacía.
MsgBox "Fin del proceso."
End Sub
Lo que no queda claro es si, además, necesitas que cada vez que escribas en esa col se coloque el vínculo a la hoja cuyo nombre sea el contenido de la celda.
Comentame si necesitas más ayuda con este tema.
Mientras, te invito a mirar en mi canal (https://www.youtube.com/c/ElsaMatilde) estos 3 videos relacionados con el tema Hipervínculos:
Nº 34 (abrir otros documentos)
Nº 33 (vincular archivos a celdas)
Nº 32 (Hipervínculos a sitios web)