Generar hojas con nombre a partir de una lista

martes, marzo 25, 2008

Una de las consultas que recibo con bastante frecuencia es cómo generar hojas en un cuaderno a partir de una lista.
Supongamos que tenemos una hoja en un cuaderno con la lista de los meses del año.



Queremos que una macro genere 12 hojas en el cuaderno, un para cada mes, y les ponga el nombre del mes



Esto podemos hacerlo con una macro bastante sencilla

Sub crear_hojas()
Dim Lista As Range
Dim iX As Long

Set Lista = Application.InputBox(prompt:="Señalar rango de la lista", _
Title:="Lista de nombres", Type:=8)


Application.ScreenUpdating = False

For iX = Lista.Count To 1 Step -1
Sheets.Add.Name = Lista(iX)
Next iX

Sheets(1).Select

Application.ScreenUpdating = True

End Sub


Al correr esta macro, se abre un diálogo donde ponemos el rango que contiene la lista (pueden ser meses, días de semana o cualquier otra cosa); al pulsar el botón Aceptar



se generarán las hojas en el orden de la lista.

Si queremos usar esta macro con cierta frecuencia o pasársela a un compañero del trabajo, descubriremos que tenemos que hacer algunas mejoras.
Por ejemplo, si en el diálogo de elegir rango pulsamos Cancelar, se abrirá el editor de Vba y veremos este mensaje de error



Para evitar esto usamos la sentencia On Error

Sub crear_hojas()
Dim Lista As Range
Dim iX As Long


On Error GoTo Cancelar

Set Lista = Application.InputBox(prompt:="Señalar rango de la lista", _
Title:="Lista de nombres", Type:=8)


Application.ScreenUpdating = False

For iX = Lista.Count To 1 Step -1
Sheets.Add.Name = Lista(iX)
Next iX

Sheets(1).Select

Application.ScreenUpdating = True

Cancelar:
End Sub

Otro problema puede generarse cuando ya existe en el cuaderno una hoja con uno de los nombres que aparecen en la lista. Por ejemplo, si la hoja Enero existe antes de correr la macro, obtenemos este resultado



Para evitar este problema tendremos que hacer algunas modificaciones a nuestra macro. Primero creamos una función para verificar si una hoja a crear ya existe


Function chequear_hoja(sheetName As String) As Boolean

Dim wkb As Worksheet

On Error Resume Next
Set wkb = Sheets(sheetName)
On Error GoTo 0

chequear_hoja = IIf(Not wkb Is Nothing, True, False)
End Function

Esta función da como resultado FALSE si la hoja no existe, y TRUE si la hoja ya existe en el cuaderno.
Lo que hacemos es usar esta función (adaptada de la propuesta por Colo) en nuestra macro para crear una condición. Si la hoja no existe, entonces creamos una nueva con el nombre correspondiente; de existir, pasamos a verificar el próximo nombre en la lista.

Sub crear_hojas2()
Dim Lista As Range
Dim iX As Long


On Error GoTo Cancelar

Set Lista = Application.InputBox(prompt:="Señalar rango de la lista", _
Title:="Lista de nombres", Type:=8)


Application.ScreenUpdating = False

For iX = Lista.Count To 1 Step -1
If chequear_hoja(Lista(iX)) = False Then
Sheets.Add.Name = Lista(iX)
End If
Next iX

Sheets("Hoja1").Activate

Application.ScreenUpdating = True

Cancelar:
End Sub

El cuaderno con las macros se puede descargar aquí

Actualización del 27.03.08: archivo con la macro corregida, la hoja con la lista queda en primer lugar

Technorati Tags:

28 comments:

Palel 26 marzo, 2008 15:46  

Excelente...
¿Se podría conseguir que la hoja que contiene los nombres de las hojas a generar quedase en primer o último lugar?

Jorge L. Dunkelman 26 marzo, 2008 18:05  

En la versión original es lo que hacía. Luego fui "mejorándola" y veo que ahora la hoja con la lista no queda al principio. Veré de corregir el código en cuanto tengo un poco de tiempo.

Direccion de Obra 01 abril, 2008 01:45  

Hay una aplicacion que siempre he estado buscando y es precisamente la inversa de la del ejemplo aqui desarrollado. ¿Como hacer para generar un listado de los nombres de las diferentes hojas que se han venido creando y quisiera resumirlas en una sola hoja para crear hipervinculos como un indice de contenido?
Gracias jorge

Anónimo,  04 abril, 2008 20:18  

Hola Jorge, como se puede hacer para que a partir de una hoja modelo, que contenga algunos cuadros que sean necesarios replicarlos para utilizar su ejemplo mes a mes.
Al momento de ejecutar la macro cada hoja nueva ya contenga los cuadros que tengo como modelo.

Jorge L. Dunkelman 04 abril, 2008 23:43  

Tendrías que modificar la macro de manera que:

- copie los cuadros de la hoja modelo a la hoja nueva, es decir, agregar un sentencia que realice el copiado de los rangos con los cuadros

o

- en lugar de crear hojas nuevas, copiar la hoja modelo y cambiar el nombre. Este me parece la opción más práctica.

Anónimo,  04 junio, 2008 02:12  

Buenisimo lo tuyo. Siempre uso tus enseñanzas. En este caso me vino perfecto, le hice un retoque que me resulta muy util: las hojas nuevas salen ya con el nombre en A1.

Anónimo,  30 octubre, 2008 00:10  

Mi estimado, estoy trabajando en una variación y me gustaria saber si tiene alguna idea de la solución; tengo una hoja que alimenta una serie de plantillas, la idea es que en la hoja 1 se ingresan los datos y segun el número de filas que tengan información se van creando plantillas a partir de la que esta creada en la hoja 2 es decir a tantas filas llenas en la hoja 1 se creen tantas hojas con información y un formato preestablecido..

Jorge L. Dunkelman 06 noviembre, 2008 18:53  

Si te refieres a ir creando las hojas a medida que se ingresan los nombres, tienes que programar un evento (puede fijarte en esta nota).

Anónimo,  20 agosto, 2009 19:28  

Estimado Jorge, de antemano muchas gracias por sus útiles consejos me han ayudado bastante.. quisiera saber si hay alguna formar de hacer lo mismo pero con libros.. es decir crear una serie de libros o cuadernos a partir de una lista con nombres...

Jorge L. Dunkelman 21 agosto, 2009 07:24  

Si, de manera similar. Para crear un cuaderno con VB usamos el método Add

Workbooks.Add

Anónimo,  23 agosto, 2009 03:13  

Estimado Jorge, he intentado hacer el cambio en el codigo para generar libros pero me bota error constantemente.. exactamente en que parte hay q poner el Workbooks.Add???

Andres 30 julio, 2013 08:19  

Los enlaces a los archivos estan caídos...podrías ser tan amable de volc¡ver a subirlos. Gracias por el buen trabajo y el aporte

Jorge Dunkelman 30 julio, 2013 14:45  

Andres, lamentablemente el sitio donde estaban almacenados cerro y no tengo las copias a mano. Espero poder subirlas en los próximos días.

Anónimo,  10 agosto, 2013 13:43  

Hola, yo tengo una consulta. Recién estoy aprendiendo a usar macros y he hecho este comando tan sencillo

Sub Einführen()
'Abrir hoja Projekte

Projekte.Visible = True

End Sub

Y ya me sale el mensaje de Error 424. A que se puede deber este problema?

Muchas Gracias

Jorge Dunkelman 10 agosto, 2013 22:57  

Te falta indicar cuál es el objeto (la hoja en tu caso). El código tiene que ser

Sheets("Projekte").Visible = True

Anónimo,  23 agosto, 2013 19:29  

Hola buen día

Necesito realizar esta misma macro, pero la unica observacion es que debe manterse un mismo formato cada vez que se genere

Jorge Dunkelman 24 agosto, 2013 08:12  

Suponiendo que tengas un rango con los nombres de las hojas que quieres crear (lstMeses, por ejemplo)
y una hoja que sea la plantilla (el formato, si es que entendí tu consulta), puedes usar esta macro

Sub copy_sheets()
Dim rngCell As Range

For Each rngCell In Range("lstMeses")
Sheets("plantilla").Copy after:=Sheets(Sheets.Count)
ActiveSheet.Name = rngCell
Next rngCell

End Sub

Anónimo,  22 octubre, 2013 16:41  

Gracias, yo tamben necesitaba esto último, le voy a poder scar mucho provecho! idolo Jorge!

Orlando León Márquez 27 enero, 2014 20:41  

HOLA JORGE DUNKELMAN
COMO PUEDO CREAR HOJAS CON EL MISMO FORMATO (SUPONGAMOS UNA PORTADA) A PARTIR DE UNA LISTA DE NOMBRE PERO CON DIFERENTE NOMBRE Y QUE LAS HOJAS SE LES NOMBRE COMO A MI LISTA.

Jorge Dunkelman 28 enero, 2014 07:03  

Eso es exactamente lo que se explica en la nota.

Orlando León Márquez 12 febrero, 2014 02:04  

Jorge como estas, oye al parece no se publico mi comentario pero ahí va de nuevo mi planteamiento.
1.- TENGO UN LIBRO DE EXCEL CON 4 HOJAS LA HOJA1 ES MI BASE DE DATOS, HOJA2 ENCABEZADO (PARTE Y/O DATOS MODIFICABLES CADA SEMANA), HOJA3 UN CONSOLIDADO DE TRABAJADORES CON ID Y HOJA4 FORMATO DE CAPTURA DE TIEMPO EXTRA; ahora lo que necesito y tu macro me ha ayudado mucho, es que de acuerdo al numero de trabajadores capturados en la hoja3 me genere por cada uno la hoja4 ej. si capturo 3 en consolidado que me genere 3 hojas4 pero que se les asigne el nombre a la celda que contiene su id. ingrese tu macro y lo hace pero me crea una hoja en blanco con el nombre de los id seleccionados, modifique la macro para copiar la hoja4 y ahora copia la hoja4 pero les asigna el nombre hoja4 (2), hoja4 (3) y asi sucesivamente, como hacerle para que cambie por el nombre del rango seleccionado de id abajo pego mi código te agradezco eres excelente en tus explicaciones.

Sub Crear_Hojas()
Dim iX As Long
Dim Lista As Range


On Error GoTo Cancelar
Set Lista = Application.InputBox(prompt:="Selecciona el rango de FICHAS", _
Title:="Lista de Fichas", Type:=8)
Application.ScreenUpdating = False
For iX = Lista.Count To 1 Step -1
If checar_hoja(Lista(iX)) = False Then
Sheets("04 Formato_Blanco").Copy after:=ActiveWorkbook.Sheets(Sheets.Count)
'Sheets.Add.Name = Lista(iX)
End If
Next iX

Sheets("03 Consolidado").Activate
Sheets("03 Consolidado").Select
Sheets("03 Consolidado").Move Before:=Sheets(3)
Application.ScreenUpdating = True

Cancelar:
End Sub

Jorge Dunkelman 12 febrero, 2014 12:03  

Orlando, me parece que tu problema reside en que por un lado estás generando una nueva hoja con

Sheets("04 Formato_Blanco").Copy after

y también generando otra hoja con

Sheets.Add.Name = Lista(iX)

Para cambiar el nombre te basta con Activesheet.Name=...

Tengo un pedido: por favor, usa las mayúsculas y los signos de puntuación. Se hace muy difícil leer lo que escribes.

SebaXX 14 marzo, 2014 17:28  

Estimado, existe alguna macro u otra forma, de que pueda cambiar simultaneamente los nombres de las hojas en un libro de excel.

Sucede que tengo varios libros con unas 300 hojas cada uno de registros diarios y deseo que cada libro tenga una hoja resumen en la que muestre los valores de esos registros. Por esto, necesito cambiar los nombres de las hojas, estas se encuentra en orden por lo tanto me serviria que la primera hoja se llame 001, la segunda hoja 002, la hoja número cien se llame 100, etc.

Como verá es mucho trabajo hacerlo una por una, por lo que si me puede ayudar se lo agradecería, he buscado arduamente en la red y no he encontrado como hacerlo.

Jorge Dunkelman 14 marzo, 2014 18:31  

Hola,

puedes hacerlo con esta macro

Sub renameSheets()
Dim iX As Integer


For iX = 1 To ActiveWorkbook.Sheets.Count
Sheets(iX).Name = Format(iX, "000")
Next iX

End Sub

Esta macro cambia los nombres de todas las hojas del cuaderno, por lo que te sugiero que hagas un backp del original antes de usarlo.

SebaXX 15 marzo, 2014 02:58  

Muchas gracias! si funciono!!!

Te molesto nuevamente. Hay unos libros que tienen algunas hojas A-1, B-1, A-2, A-3, B-2, A-4, A-5, A-6, A-7, B-3, etc....

Cómo podria hacer para que elimine automáticamente las hojas que empiezan con una determinada letra, como en el ejemplo que señale, eliminar todas las hojas que empiezan con letra B o eliminar todo excepto las hojas que empiezan con letra A.

Gracias nuevamente!!!!

Jorge Dunkelman 15 marzo, 2014 19:33  

Seba, no es molestia pero el lugar más indicado para ese tipo de consultas son los foros (de Excel y de Vba), ya que esto es un blog y los comentarios se deben referir al contenido y tener algún interés general, no partidular.
En cuanto al código es sencilloÑ

Sub eliminar_hojas()
Dim sh As Worksheet

Application.DisplayAlerts = False
For Each sh In ActiveWorkbook.Worksheets
If Left(sh.Name, 1) = "A" Then
sh.Delete
End If
Next sh
Application.DisplayAlerts = True

End Sub

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP