Hola Andy:

Hay dos enfoques.

En el primero, que soluciona parcialmente tu pregunta, sólo necesitas la función INDIRECTO.

Suponiendo que tus hojas se llaman Hoja1, Hoja2... Y que en la columna A del índice, como pones en tu ejemplo, sólo figura el nº de hoja, en la celda B2 la fórmula sería: "=INDIRECTO("Hoja" & A2 & "! B4"). Así, el parámetro de "Indirecto", compone el nombre de la hoja.

Sin embargo, esta fórmula puedes adaptarla a cualquier situación. Si A2 ya contiene el nombre completo de la hoja, entonces la fórmula es aún más sencilla: "=INDIRECTO(A2 & "! B4").

Esta solución no incluye macros, y requiere que tú rellenes la columna A con los nuevos nombres de hoja.

El segundo enfoque supone incluir una macro como la que sigue:

Sub hojas()
Dim hoja As Worksheet
Dim i As Integer
i = 2
Worksheets("Hoja1"). Rows("2:100"). Delete
' Si va a haber más de 99 hojas en el libro, cambiar el máximo
For Each hoja In ThisWorkbook. Worksheets
If Not hoja. Name = "Hoja1" Then
With Hoja1
. Cells(i, 1) = hoja. Name
. Cells(i, 2). FormulaR1C1 = "=INDIRECT(RC[-1]&""! B4"")"
End With
i = i + 1
End If
Next hoja
End Sub

Prefiero incluir la fórmula en la columna B, porque si cambias el contenido de la celda de B4 en cualquier hoja, se verá inmediatamente reflejado en el índice. Donde mi propuesta pone "Hoja1", debes colocar el nombre de la hoja inicial.

Si quieres añadir un "toque" más, puede incorporar un hipervínculo para que el usuario vaya directamente a la hoja que desee haciendo click sobre el nombre. Para ello, basta añadir la siguiente línea:

. Hyperlinks. Add Anchor:=. Cells(i, 1), Address:="", _
SubAddress:=hoja. Name & "! A1", TextToDisplay:=. Cells(i, 1). Text

... Inmediatamente antes de "End With"

Por último, si quieres "que automáticamente aparezca el Nombre de cada hoja que agrego", deberías incluir en el módulo ThisWorkbook una llamada a la macro en el evento NewSheet:

Private Sub Workbook_NewSheet(ByVal Sh As Object)
hojas
End Sub

Esto te garantiza que inmediatamente que crees una hoja, el índice se actualizará. Como la celda B4 de la nueva hoja estará vacía, el "trabajo" contendrá un cero. Pero en cuanto coloques el texto en B4, tendrás OK el índice (Si te molesta el cero, puedes dar a las celdas necesarias en la columna B un formato en que el cero sea invisible).

OJO: El editor de eMagister añade espacios tras los puntos, que es necesario eliminar si haces copia/pega del código.

¿Lo pruebas y me dices?

Saludos,

Rafael

Probé en forma rápida la primer solución y anduvo perfecto.

Probé la segunda y también fue de lujo, el único problema es que no comprendo como agregar el

" Private Sub Workbook_NewSheet(ByVal Sh As Object)
hojas
End Sub"

Para que se actualice automático, lo que hice fue crear un botón para asignarle el macro y que actualice en ese momento, pero podrías decirme como sumarlo al propio macro así me ahorro lo del botón "actualizar"?

Más allá de que respondas esto o no, te agradezco muchísimo tu ayuda, todo sirvió a la perfección a excepción de que como soy medio "lerdo" con los macros, no pude lograr aún esta última parte, pero la verdad que me has ayudado un montón de verdad. Es justo lo que precisaba.

Muchas gracias, saludos... Y si puedes sácame esa útlima duda.

Saludos

Andy,

Me alegra haber ayudado.

Mira, en el fondo el "automatismo" no sirve de mucho, porque regenera el índice INMEDIATAMENTE que creas la nueva hoja, así que siempre aparecerá "Hojax" y no será automático si cambias el nombre a la hoja. Me parece perfecto que hayas añadido un botón.

En cualquier caso, para agregar el procesado automático, en VBA sólo tienes que hacer doble click en el módulo "Thisworkbook" del libro (está dentro de "Microsoft Excel Objetos") y copiar la llamada a la macro "hojas". Sólo son esas tres líneas.

Private Sub Workbook_NewSheet(ByVal Sh As Object)
hojas
End Sub

Esto hace que cada vez que se crea una hoja nueva (evento "NewSheet"), se llama a la rutina "hojas".

En el mismo módulo puedes elegir otro evento (p. Ej. "BeforeSave") para ejecutar automáticamente "hojas" antes de guardar los cambios en la hoja:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
hojas
End Sub

Puedes probar añadiendo una hoja nueva, y al volver a la primera, verás que ya está ahí.

Durante las pruebas puedes añadir al final de la rutina "hojas", justo antes de "End Sub" la línea:

MsgBox "Indice actualizado"

Y así tienes un aviso de que se ha llamado a la macro.

¿Probaste a incluir el hipervínculo?

Saludos,

Rafael

Perfecto!

Si, probé poner el hipervínculo y anduvo impecable, solo modifiqué que en vez de pegar una sola vez el código necesario, lo hice dos veces cambiando el valor por 2 en la segunda opción. Ya que es mejor acceder a la hoja por cualquiera de los 2 lados (ya sea el nombre de la misma, o por el "trabajo" realizado)

Así quedó:

. Hyperlinks. Add Anchor:=. Cells(i, 1), Address:="", _
SubAddress:=hoja. Name & "! A1", TextToDisplay:=. Cells(i, 1). Text

. Hyperlinks. Add Anchor:=. Cells(i, 2), Address:="", _
SubAddress:=hoja. Name & "! A1", TextToDisplay:=. Cells(i, 1). Text

Ya que estamos te consulto una duda que siempre tuve y no te molesto más:

Se puede nombrar automáticamente una hoja con el valor que tenga determinada celda en la misma hoja? (Ejemplo: estoy en "hoja1" y la casilla A1 tiene el valor "Baja", y que la hoja tome ese valor como nombre)

Y se puede ordenar de mayor a menor o en órden alfabético las hojas de un libro automatica o manualmente?

Desde ya, mil gracias, me ha servido de maravilla tu ayuda.

Saludos

Hola Andy,

Me alegro del éxito. Acerca de tus dos preguntas:

1) La instrucción que buscas es, por ejemplo:

Activesheet. Name = activesheet. Range("A1"). Text

Sin embargo, sería bueno comprobar que el contenido de A1 en esa hoja sea un nombre de hoja válido. Creo que las restricciones son (pueden haber cambiado para Excel 2007):

- No más largo de 31 caracteres.
- No puede contener ninguno de los caracteres " : \ /? * [ ]"
- No puede estar en blanco

2) Para clasificar las hojas alfabéticamente puedes hacerlo "a mano", pinchando y soltando las pestañas, pero, claro está, es mejor que trabaje Excel. Un posible método es el código que sigue:

Sub Clasif_hojas(Optional tipoClasif As Variant = "A")
' Sin parámetros o con parámetro = "A", ordena ascendente
' Con parámetro distinto de "A", orden descendente
Dim i As Long, j As Long
Dim nHojas As Long

nHojas = Sheets. Count

If tipoClasif = "A" Then ' Orden ascendente
For i = 1 To nHojas
For j = i To nHojas
If UCase(Sheets(j). Name) < UCase(Sheets(i). Name) Then
Sheets(j). Move before:=Sheets(i)
End If
Next j
Next i
Else ' Orden descendente
For i = 1 To nHojas
For j = i To nHojas
If UCase(Sheets(j). Name) > UCase(Sheets(i). Name) Then
Sheets(j). Move before:=Sheets(i)
End If
Next j
Next i
End If

End Sub

Esta rutina funciona con el libro activo. Si se la llama sin parámetros (o con el parámetro = "A") el orden será de menor a mayor, y si se la llama con un parámetro distinto de "A", el orden será de mayor a menor.

Es una rutina de clasificación un poco "tosca", pero funciona bien para pocas unidades, como las hojas de un libro. Como ves, la instrucción clave es MOVE aplicada a la hoja que corresponda en cada iteración del bucle.

¿Te sirve?

Saludos,

Rafael

.

Rafael:

Eh probado las dos formas y tuvo un inconveniente, el código más largo para el ordenamiento de las hojas anda impecable. Pero no logro hacer andar el primero, aunque al principio era el que me imaginaba más sencillo.

EDIT: Ya está, borre "text" al final y listo lo hace con los valores de esas celdas.

Si preciso que lo haga automáticamente al apretar actualizar en el índice, lo agrego a ese macro y lo hace, pero como puedo lograr que lo haga en todas las hojas a la vez? Ya que de esta forma lo hace con la hoja activa

Gracias nuevamente

Hola Andy:

Entiendo que usas números para el nombre de las hojas... Por eso falló la propiedad Text. Buena solución la tuya.

En cuanto a aplicar el nombre a todas las hojas EXCEPTO a la Hoja1, es cuestión de colocar un bucle como:

For Each hoja In ThisWorkbook. Worksheets
if not hoja. Name = "Hoja1" then hoja. Name = hoja. Range("A1")
next hoja

OJO: Si vas a incluir este bucle en la misma rutina de antes, piensa el orden en que quieres hacer las cosas. Quizá:

1º- Aplicar nombres
2º- Clasificar las hojas
3º- Generar el índice con los hipervínculos

¿Vale?

Saludos,

Rafael

Sensacional, todo probado y solucionado.

La planilla quedó de lujo, muchísimas gracias por la ayuda y predisposición.

La verdad, ejemplar lo tuyo Rafael

Saludos

Hasta la vuelta ;)

Saludos a tí.

Quizá quieras cerrar el debate ¿ ?

Rafael

Solucionado