SEPARAR INFORMACION DE UNA HOJA EXCEL (Macros Excel)

Fecha de publicación: 08/03/2018

Autor: Antonio Espín Herranz

2687 visitas

Si necesita ayuda puede contratar una clase online: 15 €/hora

Contacto

En este artículo técnico vamos a ver como podemos separar la información de una hoja Excel en varias hojas bajo un criterio. Vamos a partir de una tabla de registros como la siguiente:

Tenemos información repetida en las columnas ciudad, provincia y ocupación. En este caso vamos a elegir la provincia (se puede elegir cualquiera de ellas e incluso la podría elegir el usuario) y lo que queremos es que la macro nos separe en hojas distintas las filas de cada provincia. En este caso hay 3 provincias distintas, pero el artículo lo vamos a plantear sin saber cuantas provincias tenemos. El resultado cuando termine la macro serán 3 hojas con las etiquetas Madrid, Barcelona y Cádiz y en cada una los registros correspondientes como se muestra a continuación:

Tabla de Madrid

Tabla de Barcelona

Tabla de Cádiz

Una vez tenemos claro lo que queremos hacer, deberíamos describir el proceso a realizar:

• Tenemos que recorrer la hoja principal y por cada fila hacer lo siguiente
• Coger la provincia y comprobar si tenemos ya creada una hoja con esa etiqueta
• Si la hoja no existe, la creamos y la colocamos la etiqueta (en este caso será la provincia), lo siguiente sería copiar las cabeceras de la hoja principal (origen) a la nueva hoja (destino)
• En cualquier caso (si ya tuviéramos la hoja creada con anterioridad) tenemos que copiar la fila completa y pegarla en la nueva hoja, pero teniendo en cuenta la posición que tiene que ocupar, es decir, nos vendría bien saber cual es la primera fila disponible (y no vamos a almacenar 52 variables para llevar la última fila de cada provincia).
• Iremos repitiendo el proceso para cada fila de la hoja principal

Si volvemos a leer el proceso antes detallado, vemos que tenemos que realizar una serie de tareas:

• Crear hoja
• Copiar fila
• Comprobar si existe o no una hoja
• Y el proceso principal que va a ir recorriendo línea a línea la hoja principal

Con esto vamos a tener una función llamada buscar_crea_hoja que devuelve la hoja de la provincia correspondiente. La función comprueba si existe la hoja, si no, la crea, coloca la etiqueta y copia las cabeceras. Otra función: localiza_fila_libre (que veremos dos versiones) y la macro copiar_fila y el proceso principal. También veremos una macro que borre todas las hojas creadas salvo la hoja principal Hoja1 para que no haya que borrarlas a mano.

Function buscar_crea_hoja(provincia As String, datos As Worksheet) As Worksheet
'Localiza la hoja y si no existe la crea:
Dim i As Integer
Dim hoja As Worksheet
Dim existe As Boolean
    
    i = 1
    existe = False
    
    For Each hoja In Worksheets
        If UCase(hoja.Name) = UCase(provincia) Then
            existe = True
            Exit For
        End If
    Next
    
    If Not existe Then
        Set hoja = Worksheets.Add
        hoja.Name = provincia
        
        'Copiamos todas las cabeceras a la hoja nueva:
        datos.Rows("1:1").Copy Destination:=hoja.Rows("1:1")
    End If
    
    'Devuelve el valor de la función:
    Set buscar_crea_hoja = hoja
    
End Function

La función buscar_crea_hoja recibe la provincia y un WorkSheet es la hoja principal y devuelve un objeto WorkSheet que será una referencia a la hoja de la provincia. Con el bucle: For Each hoja In Worksheets buscamos la hoja, importante: tener en cuenta que los datos pueden no estar bien tecleados, por ejemplo: Madrid, MADRID, madrid ... por eso comparamos la provincia con las etiquetas de las hojas en mayúsculas de esta forma nos podemos ahorrar errores. Si la hoja no existe la creamos y copiamos las etiquetas con esta instrucción: datos.Rows("1:1").Copy Destination:=hoja.Rows("1:1") siempre están en la fila 1. Por último, devolvemos la referencia a la hoja de la provincia (ojo, utilizar Set en la asignación, estamos con objetos)

A continuación vamos a ver dos versiones distintas de la función localizar_fila_libre, la primera de forma iterativa, empieza en la fila 1 y va buscando la primera fila libre. Y una segunda versión más potente, utiliza los desplazamientos que hacemos en Excel para moverse al final y quedarse en la última fila ocupada.Importante las dos funciones devuelven un Integer si estamos trabajando con hojas con muchos registros tener en cuenta que podemos obtener un error de overflow por la limitación de los números enteros a 32767. En este caso es mejor utilizar el tipo Long

Function localiza_fila_libre(hoja As Worksheet) As Integer
Dim fila As Integer

    fila = 1
    
    While (Not IsEmpty(hoja.Cells(fila, 1)))
        fila = fila + 1
    Wend
    
    localiza_fila_libre = fila
End Function

Function localiza_fila_libre2(hoja As Worksheet) As Integer
Dim fila As Integer

    hoja.Activate
    fila = Cells(65536, 1).End(xlUp).Row
    localiza_fila_libre2 = fila + 1

End Function

La macro copiar_fila recibe las dos hojas (la principal y la de la provincia en curso) y el número de fila que hay que copiar. La fila destino se localizará mediante una de las dos funciones anteriores (cualquiera de las dos vale). Dentro de la macro hay un fragmento de código comentado para ver como lo hace el grabador de macros (este código puede variar un poco según vayamos haciendo el proceso en el grabador). Básicamente es utilizar el método copy del objeto rows indicando origen y destino. La última instrucción: Application.CutCopyMode = False es equivalente a pulsar la tecla de escape en Excel cuando tenemos una fila o celda seleccionada y queremos anular la selección.

Sub copiar_fila(hoja_prov As Worksheet, fila As Integer, datos As Worksheet)
Dim fila_destino As Integer
Dim rango_origen As String
Dim rango_destino As String

    'Calcular la fila disponible en la hoja destino:
    fila_destino = localiza_fila_libre2(hoja_prov)

    'Montar los rangos:
    rango_origen = CStr(fila) & ":" & CStr(fila)
    rango_destino = CStr(fila_destino) & ":" & CStr(fila_destino)
    
    'Seleccionar origen y pegar en destino:
    'datos.Activate
    'datos.Rows(rango_origen).Select
    'Selection.Copy
    'hoja_prov.Activate
    'hoja_prov.Rows(rango_destino).Select
    'hoja_prov.Paste
    
    'Copiar y pegar la fila:
    datos.Rows(rango_origen).Copy hoja_prov.Rows(rango_destino)
    
    'Corta la selección:
    Application.CutCopyMode = False
End Sub

La macro que representa el proceso principal queda bastante sencilla y no necesita mucha explicación... empieza en la fila 2 y recorre todas las líneas hasta que encuentra una vacía. Para cada fila, coge la provincia, busca la hoja creándola si es necesario y avanza a la siguiente fila.

Sub proceso_principal()
'Separa las filas de los datos originales en hojas:
Dim provincia As String
Dim datos As Worksheet
Dim hoja_prov As Worksheet
Dim fila As Integer


    Set datos = Worksheets("Hoja1")
    fila = 2
    
    'Por cada provincia nueva hay que crear una hoja nueva:
    While Not IsEmpty(datos.Cells(fila, 3))
        provincia = datos.Cells(fila, 3)
        Set hoja_prov = buscar_crea_hoja(provincia, datos)

        copiar_fila hoja_prov, fila, datos
        fila = fila + 1
    Wend
    
End Sub

Y por último, una macro que nos permita borrar todas las hojas generadas salvo la principal. Viene bien si queremos repetir el proceso. Esta instrucción Application.DisplayAlerts = False evita que Excel nos esté solicitando una confirmación por cada hoja que queramos borrar. De esta forma no nos pregunta y al final volvemos habilitar las alertas.

Sub borrar_hojas()
'Borra todas las hojas menos Hoja1
    Dim i As Integer
    
    i = 1
    Application.DisplayAlerts = False
    
    While i < Worksheets.Count
        If Worksheets(i).Name <> "Hoja1" Then
            Worksheets(i).Delete
        Else
            i = i + 1
        End If
    Wend
     Application.DisplayAlerts = True
End Sub

Nada más, dejo unos enlaces a cursos de formación relacionados con este tema por si quiere ampliar sus conocimientos y un enlace para descargar el código. Va dentro de un libro Excel de la versión 2003 pero está probado hasta la versión 2016.

Cursos recomendados:

VB01 VB02

Descarga código