Consolidar / Fusionar varias hojas de cálculo en una hoja maestra usando VBA
A veces queremos combinar varias hojas en una sola para poder analizar fácilmente los datos y convertirlos en información útil. Estos artículos le dirán cómo combinar varias hojas de trabajo en una hoja de trabajo usando VBA.
Ejemplo:
Aquí he obtenido algunos datos del servidor que devuelve datos a diferentes hojas de trabajo. Agregué una hoja más y la nombré «Maestra». No importa otros nombres de hojas.
Ahora ejecute esta macro.
Sub Merge_Sheets()
Dim startRow, startCol, lastRow, lastCol As Long
Dim headers As Range
'Set Master sheet for consolidation
Set mtr = Worksheets("Master")
Set wb = ThisWorkbook
'Get Headers
Set headers = Application.InputBox("Select the Headers", Type:=8)
'Copy Headers into master
headers.Copy mtr.Range("A1")
startRow = headers.Row + 1
startCol = headers.Column
Debug.Print startRow, startCol
'loop through all sheets
For Each ws In wb.Worksheets
'except the master sheet from looping
If ws.Name <> "Master" Then
ws.Activate
lastRow = Cells(Rows.Count, startCol).End(xlUp).Row
lastCol = Cells(startRow, Columns.Count).End(xlToLeft).Column
'get data from each worksheet and copy it into Master sheet
Range(Cells(startRow, startCol), Cells(lastRow, lastCol)).Copy _
mtr.Range("A" & mtr.Cells(Rows.Count, 1).End(xlUp).Row + 1)
End If
Next ws
Worksheets("Master").Activate
End Sub
¿Cómo combinar hojas usando esta macro de VBA?
-
Inserte una nueva hoja y asígnele el nombre «Maestra» en el libro de trabajo. Cambie el nombre más tarde si lo desea.
-
Inserte un módulo en el editor VBA y copie el código VBA anterior.
-
Ejecute la macro.
-
Se le pedirá que seleccione títulos. Seleccione el título y presione Aceptar.
Y ya está. Todas las hojas se fusionan en master.
¿Cómo funciona?
Supongo que conoce los conceptos básicos de la creación de objetos y variables en VBA. en la primera parte hemos creado objetos y variables que necesitaremos en nuestras operaciones.
Bueno, la mayoría de las cosas que he explicado usando comentarios en código vba.
Veamos la parte principal de este código vba.
For Each ws In wb.Worksheets
'except the master sheet from looping
If ws.Name <> "Master" Then
ws.Activate
lastRow = Cells(Rows.Count, startCol).End(xlUp).Row
lastCol = Cells(startRow, Columns.Count).End(xlToLeft).Column
'get data from each worksheet and copy it into Master sheet
Range(Cells(startRow, startCol), Cells(lastRow, lastCol)).Copy _
mtr.Range("A" & mtr.Cells(Rows.Count, 1).End(xlUp).Row + 1)
End If
Next ws
En artículos anteriores aprendimos cómo recorrer las hojas y cómo obtener la última fila y columna usando vba.
Aquí estamos recorriendo cada hoja en el libro de trabajo principal usando for loop.
Para cada ws en wb.Worksheets Luego excluimos la hoja «maestra» del bucle, ya que consolidaremos nuestros datos en esa hoja.
Luego obtenemos la última fila y el último número de columna.
Ahora la siguiente línea es muy importante. Hemos realizado múltiples operaciones en una sola línea.
Rango (Celdas (fila inicial, columna inicial), Celdas (fila anterior, columna final)). Copiar _ mtr.Range («A» & mtr.Cells (Rows.Count, 1) .End (xlUp) .Row + 1)
Primero formamos un rango usando startRow, startCol y lastRow y lastCol.
Range(Cells(startRow, startCol), Cells(lastRow, lastCol))
We have copied it using copy method of range.
Range(Cells(startRow, startCol), Cells(lastRow, lastCol)).Copy
We pasted it directly into first blank cell after last non blank cell in column A of master sheet (mtr.Cells(Rows.Count, 1).End(xlUp).Row + 1).
Range(Cells(startRow, startCol), Cells(lastRow, lastCol)).Copy _
mtr.Range("A" & mtr.Cells(Rows.Count, 1).End(xlUp).Row + 1)
Este ciclo se ejecuta para todas las hojas y copia los datos de cada hoja en la hoja maestra.
Finalmente, al final de la macro activamos la hoja maestra para ver el resultado.
Así que sí, chicos, así es como pueden combinar cada hoja en un libro de trabajo. Avíseme si tiene alguna consulta con respecto a este código VBA o cualquier tema de Excel en la sección de comentarios a continuación.
Descargar archivo:
`link: /wp-content-uploads-2019-11-Consolidate_Merge-multiple-worksheets-into-one-master-sheet-using-VBA.xls [__ Consolidate_Merge múltiples hojas de trabajo en una hoja maestra usando VBA]
Artículos relacionados:
Cómo recorrer las hojas
cómo obtener la última fila y columna usando vba
link: / files-workbook-and-worksheets-in-vba-add-and-save-new-workbook-using-vba-in-microsoft-excel [Add And Save New Workbook Using VBA In Microsoft Excel 2016]
link: / menus-toolbars-status-bar-in-vba-display-a-message-on-the-statusbar-using-vba-in-microsoft-excel [Mostrar un mensaje en la barra de estado de Excel VBA]
link: / general-topics-in-vba-turn-off-warning-messages-using-vba-in-microsoft-excel [Desactivar mensajes de advertencia usando VBA en Microsoft Excel 2016]
Artículos populares:
enlace: / fórmulas-y-funciones-introducción-de-vlookup-function [La función VLOOKUP en Excel]
enlace: / tips-countif-in-microsoft-excel [COUNTIF en Excel 2016]
link: / excel-formula-and-function-excel-sumif-function [Cómo usar la función SUMIF en Excel]