How do I consolidate worksheets in Excel VBA?
The examples will add a worksheet with the name RDBMergeSheet to your workbook and will copy cells Show
Important: You can also find the information from this page in this article on MSDN: Consolidating Data from Multiple Worksheets into a Summary Worksheet in Excel Download Example workbookDownload a zip file with a workbook with the code from this page to test the
examples. Copy a range of each sheetNote: This example use the function LastRow Change the range in this code line 'Fill in the range that you want to copy Sub CopyRangeFromMultiWorksheets() Dim sh As Worksheet Dim DestSh As Worksheet Dim Last As Long Dim CopyRng As Range With Application .ScreenUpdating = False .EnableEvents = False End With 'Delete the sheet "RDBMergeSheet" if it exist Application.DisplayAlerts = False On Error Resume Next ActiveWorkbook.Worksheets("RDBMergeSheet").Delete On Error GoTo 0 Application.DisplayAlerts = True 'Add a worksheet with the name "RDBMergeSheet" Set DestSh = ActiveWorkbook.Worksheets.Add DestSh.Name = "RDBMergeSheet" 'loop through all worksheets and copy the data to the DestSh For Each sh In ActiveWorkbook.Worksheets If sh.Name <> DestSh.Name Then 'Find the last row with data on the DestSh Last = LastRow(DestSh) 'Fill in the range that you want to copy Set CopyRng = sh.Range("A1:G1") 'Test if there enough rows in the DestSh to copy all the data If Last + CopyRng.Rows.Count > DestSh.Rows.Count Then MsgBox "There are not enough rows in the Destsh" GoTo ExitTheSub End If 'This example copies values/formats, if you only want to copy the 'values or want to copy everything look at the example below this macro CopyRng.Copy With DestSh.Cells(Last + 1, "A") .PasteSpecial xlPasteValues .PasteSpecial xlPasteFormats Application.CutCopyMode = False End With 'Optional: This will copy the sheet name in the H column DestSh.Cells(Last + 1, "H").Resize(CopyRng.Rows.Count).Value = sh.Name End If Next ExitTheSub: Application.Goto DestSh.Cells(1) 'AutoFit the column width in the DestSh sheet DestSh.Columns.AutoFit With Application .ScreenUpdating = True .EnableEvents = True End With End Sub Tips to change the examplesIn the example above I copy the range A1:G1 but you can also use Set CopyRng = sh.UsedRange Set CopyRng = sh.Range("A1").CurrentRegion Set CopyRng = sh.Rows("1") Copy only values or
everything CopyRng.Copy With DestSh.Cells(Last + 1, "A") .PasteSpecial xlPasteValues .PasteSpecial xlPasteFormats Application.CutCopyMode = False End With With With CopyRng DestSh.Cells(Last + 1, "A").Resize(.Rows.Count, _ .Columns.Count).Value = .Value End With If you only want to copy the values. Or Replace this : CopyRng.Copy With DestSh.Cells(Last + 1, "A") .PasteSpecial xlPasteValues .PasteSpecial xlPasteFormats Application.CutCopyMode = False End With With CopyRng.Copy DestSh.Cells(Last + 1, "A") If you want to copy everything Do not loop through all sheets in the workbook Replace this line: Replace this line: Replace this line: Replace this line: Copy from row 2 till the last row with dataNote: This example use the
function LastRow In example 1 you can see that you can copy all cells on a worksheet with this line: But what if we do not want to copy the same header row each time. Change the start row in the macro if you want to start in a different row. 'Fill in the start
row If you want to copy the header row in the first row of the RDBMergeSheet then copy the code below if each worksheet have the same headers after this line : If sh.Name <> DestSh.Name Then 'Copy header row, change the range if you use more columns If WorksheetFunction.CountA(DestSh.UsedRange) = 0 Then sh.Range("A1:Z1").Copy DestSh.Range("A1") End If Example macro : Sub CopyDataWithoutHeaders() Dim sh As Worksheet Dim DestSh As Worksheet Dim Last As Long Dim shLast As Long Dim CopyRng As Range Dim StartRow As Long With Application .ScreenUpdating = False .EnableEvents = False End With 'Delete the sheet "RDBMergeSheet" if it exist Application.DisplayAlerts = False On Error Resume Next ActiveWorkbook.Worksheets("RDBMergeSheet").Delete On Error GoTo 0 Application.DisplayAlerts = True 'Add a worksheet with the name "RDBMergeSheet" Set DestSh = ActiveWorkbook.Worksheets.Add DestSh.Name = "RDBMergeSheet" 'Fill in the start row StartRow = 2 'loop through all worksheets and copy the data to the DestSh For Each sh In ActiveWorkbook.Worksheets If sh.Name <> DestSh.Name Then 'Find the last row with data on the DestSh and sh Last = LastRow(DestSh) shLast = LastRow(sh) 'If sh is not empty and if the last row >= StartRow copy the CopyRng If shLast > 0 And shLast >= StartRow Then 'Set the range that you want to copy Set CopyRng = sh.Range(sh.Rows(StartRow), sh.Rows(shLast)) 'Test if there enough rows in the DestSh to copy all the data If Last + CopyRng.Rows.Count > DestSh.Rows.Count Then MsgBox "There are not enough rows in the Destsh" GoTo ExitTheSub End If 'This example copies values/formats, if you only want to copy the 'values or want to copy everything look below example 1 on this page CopyRng.Copy With DestSh.Cells(Last + 1, "A") .PasteSpecial xlPasteValues .PasteSpecial xlPasteFormats Application.CutCopyMode = False End With End If End If Next ExitTheSub: Application.Goto DestSh.Cells(1) 'AutoFit the column width in the DestSh sheet DestSh.Columns.AutoFit With Application .ScreenUpdating = True .EnableEvents = True End With End Sub Copy a range/column after the last column with dataNote: This example use the function LastCol This example copy column A from each sheet after the last column with data on the DestSh. I use A:A to copy the whole column but you can also use a range like A1:A10. Use A:C if you want to copy more columns. Change it here 'Fill in the column(s) that you want to copy Remember that Excel 97-2003 have only 256 columns. Sub AppendDataAfterLastColumn() Dim sh As Worksheet Dim DestSh As Worksheet Dim Last As Long Dim CopyRng As Range With Application .ScreenUpdating = False .EnableEvents = False End With 'Delete the sheet "RDBMergeSheet" if it exist Application.DisplayAlerts = False On Error Resume Next ActiveWorkbook.Worksheets("RDBMergeSheet").Delete On Error GoTo 0 Application.DisplayAlerts = True 'Add a worksheet with the name "RDBMergeSheet" Set DestSh = ActiveWorkbook.Worksheets.Add DestSh.Name = "RDBMergeSheet" 'loop through all worksheets and copy the data to the DestSh For Each sh In ActiveWorkbook.Worksheets If sh.Name <> DestSh.Name Then 'Find the last Column with data on the DestSh Last = LastCol(DestSh) 'Fill in the column(s) that you want to copy Set CopyRng = sh.Range("A:A") 'Test if there enough rows in the DestSh to copy all the data If Last + CopyRng.Columns.Count > DestSh.Columns.Count Then MsgBox "There are not enough columns in the Destsh" GoTo ExitTheSub End If 'This example copies values/formats and Column width CopyRng.Copy With DestSh.Cells(1, Last + 1) .PasteSpecial 8 ' Column width .PasteSpecial xlPasteValues .PasteSpecial xlPasteFormats Application.CutCopyMode = False End With End If Next ExitTheSub: Application.Goto DestSh.Cells(1) With Application .ScreenUpdating = True .EnableEvents = True End With End Sub Common Functions required for all routinesFunction LastRow(sh As Worksheet) On Error Resume Next LastRow = sh.Cells.Find(What:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 End Function Function LastCol(sh As Worksheet) On Error Resume Next LastCol = sh.Cells.Find(What:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Column On Error GoTo 0 End Function How do I consolidate sheets in Excel VBA?Merging all sheets of active workbook into one sheet with VBA. Activate the workbook you want to combine its all sheets, then press Alt + F11 keys to open Microsoft Visual Basic for Applications window.. In popping window, click Insert > Module to create a new Module script.. Copy below code and paste them to the script.. How do I consolidate data from multiple worksheets in Excel VBA?Combine by category. Open each source sheet.. In your destination sheet, click the upper-left cell of the area where you want the consolidated data to appear. ... . On the Data tab, in the Data Tools group, click Consolidate.. In the Function box, click the function that you want Excel to use to consolidate the data.. How do I apply all worksheets in the same VBA?Here are the steps to do this:. Go to the Developer tab.. Click on the Visual Basic option. ... . In the Project Explorer pane in the VB Editor, right-click on any object for the workbook in which you want to insert the code. ... . Go to Insert and click on Module. ... . Copy and paste the code in the module window.. How to merge multiple sheets with different headers in Excel VBA?Combine tables in Excel by column headers. On your Excel ribbon, go to the Ablebits tab > Merge group, and click the Combine Sheets button:. Select all the worksheets you want to merge into one. ... . Choose the columns you want to combine, Order ID and Seller in this example:. Select additional options, if needed.. |