Sección de Artículos

Consolidar datos de múltiples hojas a un nuevo libro sin realizar cálculos


Consolidar datos de múltiples hojas a un nuevo libro sin realizar cálculos


avatar
(Programación)
Escrito el 18-10-2016 a las 10:41

Si necesitamos unir los datos de varias listas localizadas en hojas de cálculo diferentes, con la finalidad de consolidar toda la información de trabajo en una sola tabla y así poder aplicar cálculos, filtros, ordenaciones, agrupaciones, etc… sobre todos los registros, podemos utilizar la orden ‘Consolidar’ del grupo ‘Herramientas de datos’ de Excel para resumir los resultados de datos de diferentes hojas de cálculo.  Esta orden es muy práctica y potente a la hora de resumir la información repetida por las hojas, realizando cálculos de totales.

Pero si lo que buscamos es simplemente unir, es decir; fusionar el contenido de todas las listas en una sola hoja sin realizar cálculos, tendremos que optar por otras opciones. La forma más sencilla consiste en ir copiando la información de cada hoja y pegándola en una hoja de resumen.

Sí solo tenemos dos o tres hojas con poca información en cada lista, será una tarea sencilla, pero si tenemos muchas hojas con distinta cantidad de registros por hoja, esta tarea se puede volver muy monótona y engorrosa.

Por experiencia sé que en las grandes empresas, las personas que tienen que analizar la información que se genera diaria, semanal o mensualmente, solicitan al departamento responsable de la administración de sus bases de datos, consultas de los datos que necesitan. El administrador de la BD les facilita dicha información en formato de texto plano o libro de Excel, que posteriormente tienen que ir uniendo de forma manual en un nuevo libro a base de copiar y pegar los datos.

Ejemplo

Supongamos que tenemos un libro de Excel con la información de las ventas de los 8 primeros meses del año separadas en una hoja para cada mes.

Libro ventas mensuales

En cada hoja el diseño de la tabla de datos es el mismo, pero no está en la misma posición.

En el libro existen adicionalmente otras hojas que no contienen información (vacías), que no deseamos consolidar.

Tablas de datos

Queremos consolidar los datos de cada tabla mensual en una hoja de resumen, en un libro distinto del libro actual.

Lo que os propongo es automatizar todo este proceso mediante una macro que nos permita consolidar la información de múltiples hojas en un nuevo libro distinto del que contiene los datos, sin realizar cálculos.

Las ventajas que nos aportará esta macro serán:

  • Automatizar todo el proceso de selección, copiar y pegar de cada hoja.
  • No tener en cuenta la cantidad de hojas que se tengan que unir.
  • Separar la información de resumen de las listas de datos originales.

La base es sencilla, simplemente necesitamos ir copiando la información de cada hoja e ir pegándola en la hoja de otro libro.

Macro ConsolidarHojas

Consideraciones

  • Cada hoja debe de tener únicamente la tabla de datos con una fila de encabezados con el nombre de columna, sin que existan otros datos alrededor.
  • Los datos de todas las hojas deben de tener los mismos encabezados de columna.
  • No es obligatorio que la tabla de datos esté en la misma posición en cada hoja.

Configuraciones

Define tres constantes de trabajo configurables:

LIBRO_RESUMEN -> Nombre del libro de consolidación.
HOJA_RESUMEN -> Nombre de la hoja de LIBRO_RESUMEN donde se realizará la consolidación de datos.
CELDA_PEGADO -> Celda de HOJA_RESUMEN, a partir de la cual se irán pegando los datos de las tablas de datos.

Proceso de ejecución

Diagrama de ejecución

Código VBA

Macro ConsolidarHojas

' CONSTANTES DE TRABAJO
Const LIBRO_RESUMEN = "Totales" ' Nombre del libro de consolidación 
Const HOJA_RESUMEN = "Resumen"  ' Nombre de la hoja donde se realizará la consolidación de datos 
Const CELDA_PEGADO = "$C$5"     ' Celda a partir de la cual se iran pegando los datos del resto de las hojas
' OBJETOS WORKBOOK DE TRABAJO
Dim ObjLibroDatos As Workbook   ' Libro actual desde el que se ejecuta la macro
Dim ObjLibroResumen As Workbook ' Nuevo libro con 1 hoja donde se realiza la consolidación
' ------------------------------------------------------------------------------------------------
Sub ConsolidarHojas()
Dim filas As Long           ' Contador de filas copiadas
Dim CpEncabezados As Boolean  ' Flag que nos indica si se tienen que copiar los encabezados
Dim UltimaFila As Long      ' Fila a partir de la que se tienen que pegar los datos
Dim PrimeraCelda As String  ' Primera celda con datos de la hoja
Dim Hojas As Integer        ' Contador de hojas procesadas  

   ' Inicializamos las variables
  filas = 0
  CpEncabezados = True
  UltimaFila = 1
' Desactivamos la actualziación de pantalla Application.ScreenUpdating = False ' Inicializamos los objetos Workbook de origen y destino con los que se va a trabajar InicializaObjsLibro ' Recorremos la colección de hojas del libro de datos For Each Hoja In HojasLibroDatos() ' Obtenemos la dirección de la 1ª celda con datos de la hoja PrimeraCelda = PrimerValorHoja(Hoja.Name) ' Si la hoja contiene datos If PrimeraCelda <> "" Then ' Si el indicador para copiar los encabezados está activado If CpEncabezados Then ' Copiamos los encabezados de las columnas a la hoja de resumen CopiaEncabezados Hoja.Name, PrimeraCelda, HOJA_RESUMEN, CELDA_PEGADO ' Desactivamos el indicador CpEncabezados = Not CpEncabezados End If ' Calculamos la posición donde tendremos que pegar lo que vamos a copiar UltimaFila = UltimaFila + filas ' Copiamos los datos de la lista descartando los encabezados a la hoja de resumen y obtenemos el total de celdas copiadas filas = CopiaLista(Hoja.Name, PrimeraCelda, HOJA_RESUMEN, CELDA_PEGADO, UltimaFila) ' Incrementamos el contador de hojas procesadas Hojas = Hojas + 1 End If Next
' Activamos la primera hoja del libro de datos ActivaHojaDatos (1) ' Guardamos el libro Resumen If (GuardaLibroConsolidacion()) Then ' Calculamos el total de filas que hemos pegado en Resumen y mostramos mensaje de información UltimaFila = UltimaFila + filas - 1 ' Mostramos mensaje de información MsgBox "Se ha creado el libro '" & NombreLibroConsolidacion() & "' con el resultado de la consolidación." & vbCrLf & vbCrLf & _ "Se han procesado un total de " & Hojas & " hojas" & vbCrLf & _ "Se han consolidado un total de " & UltimaFila & " filas", vbInformation, "CONSOLIDAR HOJAS" Else ' Mostramos mensaje de información, indicando que no se ha guardado el libro de consolidación MsgBox "El libro de consolidación no se ha guardado a petición del usuario" & vbCrLf & vbCrLf & _ "Se han procesado un total de " & Hojas & " hojas" & vbCrLf & _ "Se han consolidado un total de " & UltimaFila & " filas", vbInformation, "CONSOLIDAR HOJAS" End If ' Activamos de nuevo la actualización de pantalla Application.ScreenUpdating = True End Sub

Procedimientos y funciones de apoyo

'-------------------------------------------------------
' INICIALIZA LOS OBJETOS WORKBOOK DE TRABAJO
Sub InicializaObjsLibro()
   Set ObjLibroDatos = ActiveWorkbook            ' El libro de datos será el libro activo
   Application.SheetsInNewWorkbook = 1
   Set ObjLibroResumen = Workbooks.Add           ' El libro de resumen, se inicializa creando un libro con una hoja
   ObjLibroResumen.Sheets(1).Name = HOJA_RESUMEN ' Asignamos el nombre HOJA_RESUMEN a la única hoja del libro
End Sub 
'-------------------------------------------------------
' OBTIENE LA COLECCIÓN DE HOJAS DEL LIBRO DE DATOS
Function HojasLibroDatos() As Sheets 
   Set HojasLibroDatos = ObjLibroDatos.Sheets
End Function
'-------------------------------------------------------
' SELECCIONA UNA HOJA DEL LIBRO DE DATOS
Sub ActivaHojaDatos(Hoja As Variant)
   ObjLibroDatos.Worksheets(Hoja).Activate
End Sub
'-------------------------------------------------------
' SELECCIONA UNA HOJA DEL LIBRO DE RESUMEN
Sub ActivaHojaResumen(Hoja As Variant)
   ObjLibroResumen.Worksheets(Hoja).Activate
End Sub
'-------------------------------------------------------
' OBTIENE EL NOMBRE DEL ARCHIVO PARA EL LIBRO DE CONSOLIDACIÓN
Function NombreLibroConsolidacion()
Static LibroConsolidacion As String
   ' Si la variable no se ha inicializado, creamos nombre para el libro de consolidación con la fecha y hora
   If LibroConsolidacion = "" Then 
     LibroConsolidacion = LIBRO_RESUMEN & Format(Date, " dd-mm-yy ") & Format(Time, "hhmm") & ".xlsx" 
   End If
   NombreLibroConsolidacion = LibroConsolidacion         ' Devuelve el nombre del libro
End Function
'-------------------------------------------------------
' GUARDA EL LIBRO DE RESUMEN CON LO DATOS DE LA CONSOLIDACIÓN Y LUEGO LO CIERRA
Function GuardaLibroConsolidacion() As Boolean
On Error Resume Next
   ' Guarda el nuevo libro creado al principio de todo el proceso
   ObjLibroResumen.SaveAs Filename:=NombreLibroConsolidacion(), FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
   ' Si no se han producido errores
   If Err.Number = 0 Then
     ObjLibroResumen.Close               ' Cierra el libro
   Else
     ObjLibroResumen.Close False         ' Si se hubiera producido algún error, cierra el libro descartando los cambios
   End If
   GuardaLibroConsolidacion = (Err.Number = 0) ' Devuelve indicador del estado
End Function
'-------------------------------------------------------
' OBTIENE LA DIRECCION DE LA 1ª CELDA CON DATOS DE LA HOJA
Function PrimerValorHoja(Hoja As String) As String
Dim rango As Range

   ActivaHojaDatos (Hoja)  ' Seleccionamos la hoja que se supone que contiene datos
   Range("XX1").Select     ' Nos desplazamos hasta una celda lejana
   ' Ejecutamos el comando Find para localizar la 1ª celda que contenga cualquier dato en la hoja
   Set rango = Cells.Find(What:="*", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlPart, _
                          SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False, _
                          SearchFormat:=False)
   ' Si no se han encontrado datos
   If rango Is Nothing Then 
     PrimerValorHoja = ""             ' Devolvemos valor vacío
   Else
     PrimerValorHoja = rango.Address  ' Devolvemos la dirección de la celda seleccionada
   End If
End Function
'-------------------------------------------------------
' COPIA LOS ENCABEZADOS DE LA LISTA A LA HOJA DE RESUMEN
' Recibe: Origen -> Nombre de la hoja que contiene la lista
'         CeldaOrigen -> Celda donde empieza la lista
'         Destino -> Nombre de la hoja donde pegar los datos
'         CeldaDestino -> Celda de pegado de los datos en la hoja de Destino
Sub CopiaEncabezados(Origen As String, CeldaOrigen As String, Destino As String, CeldaDestino As String)
   ActivaHojaDatos (Origen)                   ' Selecciona la hoja que contiene la lista de registros
   Range(CeldaOrigen).Select                  ' Se sitúa en la celda donde se supone que comienzan los encabezados de columna
   Range(Selection, Selection.End(xlToRight)).Select  ' Realiza una selección hacia la derecha hasta el último valor escrito
   Selection.Copy                    ' Copia
   ActivaHojaResumen (Destino)       ' Activamos la hoja Resumen
   Range(CeldaDestino).Select        ' Se sitúa en la celda de pegado
   ActiveSheet.Paste                 ' Pega los encabezados
   Application.CutCopyMode = False   ' Desactiva el Modo Copia
End Sub
'-------------------------------------------------------
' COPIA LAS FILAS DE LA LISTA A LA HOJA DE RESUMEN DESCARTANDO LOS ENCABEZADOS DE COLUMNA
' Recibe: Origen -> Nombre de la hoja que contiene la lista
'         CeldaOrigen -> Celda donde empieza la lista
'         Destino -> Nombre de la hoja donde pegar los datos
'         CeldaDestino -> Celda de pegado de los datos en la hoja de Destino
'         DesplazamientoFilas -> Desplazamiento a partir de CeldaDestino para pegar los datos
'           Este último argumento nos indica cuantas filas hay ya ocupadas en el rango de destino
Function CopiaLista(Origen As String, CeldaOrigen As String, Destino As String, CeldaDestino As String, DesplazamientoFilas As Long) As Long
Dim filas As Long

   ActivaHojaDatos (Origen)    ' Seleccionamos la hoja de datos
   ' Selecciona la 1ª celda con datos de la lista y realiza un desplazamiento de una fila ya que
   ' se supone que la primera fila contiene los nombres de columna
   Range(CeldaOrigen).Offset(1, 0).Range("A1").Select
   ' Ejecutamos la selección Fin+→ y Fin+↓ y copiamos los datos seleccionados
   Range(Selection, Selection.End(xlToRight)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Selection.Copy 
   filas = Selection.Rows.Count  ' Obtenemos el total de celdas seleccionadas 
   ' Este valor se utilizará en la siguiente copia para calcular la nueva posición donde pegar los datos
   ActivaHojaResumen (Destino)   ' Activamos la hoja Resumen
   ' No desplazamos hasta la fila de pegado
   ' Selecciona la celda de pegado en la hoja de resumen y realiza un desplazamiento de x filas
   ' Cada vez que pega datos, la fila de pegado irá avanzando para el próximo pegado
   Range(CeldaDestino).Offset(DesplazamientoFilas, 0).Range("A1").Select 
   ActiveSheet.Paste                ' Pega los datos
   Application.CutCopyMode = False  ' Desactiva el Modo Copia
   CopiaLista = filas               ' Devolvemos el total de filas copiadas
End Function
Plugin ValoracionesDescargar documento y Macro ConsolidarHojas de Ejemplo