Crear hojas con un clic

Vamos a profundizar un poco más, sobre algo que ya había hablado en otro artículo, cuando explicaba como verificar si existe una hoja de cálculo.

Para ello, vamos a resolver el ejercicio que nos plantea Pedro, a través de un comentario que ha dejado en el blog. El enunciado es este:

Dada una hoja índice cuya primera columna contiene diversos "nombre", crear una macro que realice lo siguiente:

Al hacer clic sobre cualquier "nombre" de esta primera columna:

1.- Crear una nueva hoja (si no existe) con ese "nombre" a partir de otra hoja (plantilla vacía) ya existente, es decir, copiar plantilla y renombrar.

2.- En esa nueva hoja, poner como título en una celda el "nombre" de la hoja.

3.- Ir a esa hoja.

Si la hoja ya existía con anterioridad, entonces ir a esa hoja.

Comenzaremos diciendo que el punto 1 puede interpretarse de varias formas. ¿Los nombres serán hipervínculos, y al cliquear sobre ellos, se ejecutará el macro, o no serán hipervínculos, y solo nos bastará con seleccionar la celda que contiene el dato, para que se ejecute el macro?. Yo he optado por esta segunda opción, porque los hipervínculos no me parecen tan elegantes.

Antes de comenzar, un par de observaciones imprescindibles:

Para que se vea que realmente cogemos una hoja dada, la copiamos y la pegamos cambiándole el nombre a ésta última, tal y como se indica en el punto 1 del enunciado, he puesto de color rojo la hoja que hará de plantilla. Para darle color a una hoja, solo hay cliquear con el botón derecho del mouse sobre la hoja cuyo color queramos cambiar, y seleccionaremos la opción Color de etiqueta.... Desde ahí podremos seleccionar el color que queramos darle a la pestaña.


Como veréis en el macro, en lugar de utilizar el nombre visible de la pestaña, para llamar a la hoja 2 (la que hace de plantilla), he utilizado el nombre interno que se ve en VBA, de esa forma si alguien cambia el nombre a la hoja (desde la pestaña), no tendremos problemas con copiar y pegar la hoja, pues su nombre interno seguirá siendo el mismo.


Para que podamos resolver el ejercicio, es lógico que el libro de excel que tengamos, deba contener como mínimo dos hojas (dos pestañas). La primera será el índice, donde estarán los nombres en la primera columna, y la segunda hoja será la que hará de plantilla. En el ejemplo que he colgado con el ejercicio resuelto, he creado un libro de excel con cinco hojas.

Vayamos a lo realmente interesante, la solución al ejercicio. Para ello, tan solo tendremos que entrar en modo VBA, ya sabéis, Atl+F11, y sobre la hoja que hará de índice, es decir, la hoja en cuya primera columna estarán los nombres, copiaremos este procedimiento (ojo, este procedimiento no deberá ir en la sección de módulos, sino en la hoja que hará de índice):

Private Sub Worksheet_SelectionChange(ByVal Target As Range) On Error Resume Next 'ocultamos el procedimiento, para que 'no se vea la ejecución del macro Application.ScreenUpdating = False 'pasamos a una variable el rango de datos 'en este caso toda la columna A. Si solo fuera 'un área determinada entre unas pocas celdas, 'tendríamos que cambiar el rango, de tal forma que 'si por ejemplo fuese de A1 a A20, pondríamos esto: 'Set datos = Range("A1:A20") Set datos = Range("A:A") 'Si seleccionamos una celda de ese rango '(en este caso, toda la columna A), y además 'esa celda no está vacía (porque si está vacía 'no crearemos una hoja sin nombre), entonces... If Union(Target, datos).Address = datos.Address And ActiveCell <> "" Then 'creamos una hoja con el nombre que haya 'en esa celda que hemos seleccionado, siempre 'y cuando no exista ya, partiendo de otra 'hoja ya existente. hoja_de_calculo = ActiveCell.Value 'seleccionamos la hoja con ese nombre Sheets(hoja_de_calculo).Select 'si no existe esa hoja, la creamos 'a partir de otra ya existente If ActiveSheet.Name <> hoja_de_calculo Then 'quitaremos los caracteres no permitidos en 'el nombre de las hojas, por si las moscas hoja_de_calculo = Replace(hoja_de_calculo, ":", "") hoja_de_calculo = Replace(hoja_de_calculo, "/", "") hoja_de_calculo = Replace(hoja_de_calculo, "\", "") hoja_de_calculo = Replace(hoja_de_calculo, "?", "") hoja_de_calculo = Replace(hoja_de_calculo, "*", "") hoja_de_calculo = Replace(hoja_de_calculo, "[", "") hoja_de_calculo = Replace(hoja_de_calculo, "]", "") 'volvemos a comprobar que queda "algo" después de 'eliminar los carecteres raros If hoja_de_calculo <> "" Then 'copiaremos la Hoja2 (nombre interno VBA) 'así si le cambian el nombre a la hoja (pestaña) 'no tendremos ningún problema Hoja2.Select 'la pegamos al final (tras la última hoja) Hoja2.Copy After:=Sheets(Sheets.Count) 'le cambiamos el nombre, y le pondremos 'el que figura en la celda seleccionada 'pero para evitar problemas con la longitud 'del nombre que haya en la celda, limitaremos 'el nombre de la hoja, a los primeros 31 caracteres, ActiveSheet.Name = Left(hoja_de_calculo, 31) 'Ponemos en una celda, el nombre 'de la hoja de cálculo (por ejemplo en A1) Range("A1").Select ActiveCell = hoja_de_calculo End If Else 'si ya existe una hoja con ese nombre, 'nos situamos en ella hoja_de_calculo.Select End If 'finalizamos el condicional que comprueba 'si nos hemos situado en una celda del rango End If 'limpiamos el objeto Set datos = Nothing 'Mostramos el procedimiento Application.ScreenUpdating = True End Sub

Desde aquí podéis descargar el fichero de excel, con el ejercicio resuelto.