Buenas. 
Tengo en un excel una hoja estilo menú en la que quiero colocar una lista que me lleve a diferentes hojas. El problema es que hay hojas que aún no están creadas y no sé cómo hacerlo. Me explico:

En dicho excel se introducen gastos de empresas y beneficios de diferentes ventas. Pues bien, yo tengo dos hojas fijas: Empresa1 y Empresa2 y dos ventas fijas Venta1 y Venta2, y en el menú tengo un macro que me genera o bien una nueva empresa que se sitúa en medio de las fijas, o bien una nueva venta que se sitúa en medio de las fijas. De forma visual, las hojas de mi excel quedan así:
Menú principal, Empresas, NuevaEmpresa, Empresa1, [Aquí se generan nuevas empresas], Empresa2, Gasto conjunto de empresas, Ventas, NuevaVenta, Tienda, [Aquí se generan nuevas ventas], Venta1, Venta2, Gasto conjunto de ventas.

Tengo esas hojas por lo siguiente:
Desde el menú puedes acceder a las otras hojas gracias a enlaces.
Las hojas: Empresas y Ventas es donde quiero que se muestren todas las empresas y ventas que hay, respectivamente. Además, en estas hojas tengo los respectivos macros que generan ventas y empresas.
Las hojas: NuevaEmpresa y NuevaVenta son las que mi macro copia, ya que siempre permanecerán sin datos.
Y dicha macro va generando empresas o ventas según corresponda.
La macro que genera empresas es bastante sencilla:

Sub GenerarNuevaEmpresa()

' Sub GenerarNuevaEmpresa Macro
' Esta macro genera una nueva empresa a partir de la hoja NuevaEmpresa.
'
' Acceso directo: CTRL+e
'
    Sheets("NuevaEmpresa").Select
    Sheets("NuevaEmpresa").Copy After:=Sheets(5)
End Sub

Con ella copio la hoja NuevaEmpresa y la coloco siempre después de la hoja número 5 (La coloco siempre después de la Empresa1).

La macro que genera ventas es así:

Sub GenerarNuevaVenta()
'
' GenerarNuevaVenta Macro
'

'
    Sheets("NuevaVenta").Select
    Sheets("NuevaVenta").Copy After:=Sheets("Tienda")
End Sub

Con ella copio la hoja NuevaVenta y la coloco después de la hoja Tienda (Esta hoja nunca cambiará su nombre, por ello coloco las nuevas empresas después de esta hoja).

 

Entonces como este excel lo voy a entregar, no es para mí, me gustaría que tenga una apariencia más ''bonita'' y para ello voy a ocultar el panel de hojas, por tanto en el menú me gustaría crear dos lista desplegables: una que me lleve a las empresas y otra a las ventas que yo quiero, y que se actualice según vaya añadiendo empresas o ventas.
Ejemplo:
Tengo dos empresas. Entonces la lista de empresas del menú me da las opciones: Empresa1, Empresa2.
Y luego, si genero una Empresa3, entonces quiero que la lista de empresas del menú se actualice a: Empresa1, Empresa2, Empresa3.

He pensado en darle un patrón al excel para que genere la lista. Se me ha ocurrido es decirle que haga una lista de Empresas que empieza en Empresa1 y que termina en la hoja anterior a Gasto de empresas, y para ventas otra lista que comienza en Tienda y que termina en la hoja anterior a Gasto conjunto de ventas, pero desconozco si es posible esto o cómo hacerlo...
Gracias de antemano.

considera que la forma de exponer tu situación sigue siendo una "adivinanza" (?)

y no has dado respuesta, comentario u opinión a esta propuesta (?)

Estimado @96andres96, en resumen, lo que quieres es tener un menú donde te vaya mostrando las nuevas hojas:

algo así se me ocurre:

Private Sub ComboBox1_Change()
Sheets(ComboBox1.Text).Select
End Sub
Private Sub UserForm_Initialize()
fil = 1
For s = 1 To Sheets.Count
    If Sheets(s).Name <> "Menu" Then
        Hoja1.Range("A" & fil) = Sheets(s).Name
        fil = fil + 1
    End If
Next

uf = Hoja1.Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To uf
ComboBox1.AddItem Hoja1.Range("A" & i)
Next
Columns(1).Clear
End Sub

te adjunto el ejemplo y adaptalo en tu proyecto,

 

Saludos,

Libro1.xlsm

Hace 9 minutos , Héctor Miguel dijo:

considera que la forma de exponer tu situación sigue siendo una "adivinanza" (?)

y no has dado respuesta, comentario u opinión a esta propuesta (?)

Hola, Héctor. No me había salido ninguna notificación como que me habían respondido, por eso opté por seguir buscando. Revisaré lo que me han comentado ahí y lo que me comentan aquí

Hace 2 minutos , DiegoLG dijo:

Estimado @96andres96, en resumen, lo que quieres es tener un menú donde te vaya mostrando las nuevas hojas:

algo así se me ocurre:

Private Sub ComboBox1_Change()
Sheets(ComboBox1.Text).Select
End Sub
Private Sub UserForm_Initialize()
fil = 1
For s = 1 To Sheets.Count
    If Sheets(s).Name <> "Menu" Then
        Hoja1.Range("A" & fil) = Sheets(s).Name
        fil = fil + 1
    End If
Next

uf = Hoja1.Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To uf
ComboBox1.AddItem Hoja1.Range("A" & i)
Next
Columns(1).Clear
End Sub

te adjunto el ejemplo y adaptalo en tu proyecto,

 

Saludos,

Libro1.xlsm 30.92 kB · 0 descargas

Gracias por responder. Revisaré lo que me has propuesto para ver si me sirve ?

En 12/11/2020 at 0:32 , DiegoLG dijo:

Estimado @96andres96, en resumen, lo que quieres es tener un menú donde te vaya mostrando las nuevas hojas:

algo así se me ocurre:

Private Sub ComboBox1_Change()
Sheets(ComboBox1.Text).Select
End Sub
Private Sub UserForm_Initialize()
fil = 1
For s = 1 To Sheets.Count
    If Sheets(s).Name <> "Menu" Then
        Hoja1.Range("A" & fil) = Sheets(s).Name
        fil = fil + 1
    End If
Next

uf = Hoja1.Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To uf
ComboBox1.AddItem Hoja1.Range("A" & i)
Next
Columns(1).Clear
End Sub

te adjunto el ejemplo y adaptalo en tu proyecto,

 

Saludos,

Libro1.xlsm 30.92 kB · 2 descargas

 

 

Gracias por tu respuesta. Me ha parecido muy ingeniosa. Sin embargo, quería saber si existe la posibilidad de que en el for no me llegue a la última hoja. En el código tuyo propuesto pones que el for vaya desde la hoja s=1 y que cuente el número de hojas y llegue hasta esa.

En mi excel tengo estas hojas:

Menú principal, Empresas, NuevaEmpresa, Empresa1, [Aquí se generan nuevas empresas], Empresa2, Gasto conjunto de empresas, Ventas, NuevaVenta, Tienda, [Aquí se generan nuevas ventas], Venta1, Venta2, Gasto conjunto de ventas.

Entonces utilizando ese código, observo que con el for puedo crear una lista que comience en s=4 (Empresa1) y quiero que llegue hasta la última empresa creada, y ese número va variando según añada empresas. Si solo tengo Empresa1 y Empresa 2 entonces llegará hasta la posición s=5, pero si añado por ejemplo 5 empresas, pues entonces en mi excel tendría estas hojas:

Menú principal, Empresas, NuevaEmpresa, Empresa1, Empresa2, Empresa3, Empresa4,Gasto conjunto de empresas, Ventas, NuevaVenta, Tienda, [Aquí se generan nuevas ventas], Venta1, Venta2, Gasto conjunto de ventas.

y entonces yo querría que mi for fuese desde el s=4 (Empresa1) hasta s=7.

¿Hay alguna forma de poder hacer que el for llegue hasta la hoja anterior a una con nombre específico? Si pudiese decir que el for llegase hasta la hoja anterior a 'Gasto conjunto de empresas' me serviría, ya que esta hoja es la siguiente a la última empresa generada.

He pensado en poner esto, pero se me cuelga el programa, y no sé qué estoy haciendo mal.
   

For s = 5 To Sheets.Count
    Do Until Sheets(s).Name <> "Gasto conjunto de empresas"
    If Sheets(s).Name <> "Menú principal" Then
        Hoja2.Range("U" & fil) = Sheets(s).Name
        fil = fil + 1
    End If
    Loop
Next

 

Estimado! @96andres96, hay que manejar un poco la lógica:

Si te diste cuenta, el nombre de cada hoja te concentra en la primera hoja columna A,

Lo que yo haría, es: al momento de visualizar el nombre de cada hoja, primero elimino que hojas no necesito, y considero que con eso, queda.

la verdad de tanto leer texto, me dio flojera jeje..

y si no es lo que necesitas, mejor has un ejemplo en donde obtienes la información y como debe de quedar,

Saludos,

 

Diego.

No pasa nada. Ya logre solucionar todo con unas ideas que tuve. Duda resuelta.