Combine All Open Workbooks Excel VBA Macro

We often need to gather data spread across several Excel workbooks. The following VBA macro combines all open workbooks into just one. The final number of worksheets will be the sum of sheets across all open workbooks. We may want to add code to close the workbooks when done; we can do that simply adding wb.Close within the IF statement.

Macro code:

 

  Sub CombineAllOpenWorkbooks()

 

      Dim wb As Workbook

 

      'Start looping through open workbooks and copy

      For Each wb In Workbooks

          If wb.Name <> ThisWorkbook.Name Then

              wb.Worksheets.Copy After:=ThisWorkbook.ActiveSheet

          End If

      Next wb

 

  End Sub

 

 

Macro explained:

• First we declare a workbook object variable (wb) that will represent each open workbook in the following loop.
• We start a For Each loop that will get each open workbook in the Workbooks collection.
• Then we check the workbook is not the destination workbook (Thisworkbook), by using the Name property. If so, we copy all worksheets of each open workbook after the last sheet in the destination.
• We can add one line of code to close the workbooks when done. We do that simply adding wb.Close within the IF statement.

This is how we combine all open workbooks in Excel using VBA macros.

Other examples:

• How to Compare Two Worksheets with Excel VBA macros 
  
• How to Send Email From Excel with Excel VBA macros
  
• How to Add Unique Entries Validation List with Excel VBA macros