Combining Different Excel Worksheets' Data into One Worksheet Using VBA

Suppose you need to combine many different Excel worksheets’ data into one worksheet so that you can analyze the combined data. For example, in some situations, data from different years are separately stored in different Excel worksheets, like the 2015 to 2019 World Happiness data from Kaggle. In this case, each year’s data is stored in a separate Excel worksheet. It would be convenient to automatically copy all Excel worksheets’ data into one master worksheet using just one click. VBA can help with that.

In order to combine multiple worksheets into one, you should begin by opening the VBA editor. On the “Developer” tab located on the top ribbon of Excel, you can find “Visual Basic.” Once you click “Visual Basic,” there will be a drop-down menu on the top left of the new screen where you can find “Module.” Once you click this, it opens the VBA editor. Alternatively, you can press “Alt + F11” to open the VBA editor. Then, write the following code in the VBA editor:

Sub MergeSheets()

'Set statement to assign the object to the variable
Set wb = ThisWorkbook
  
'Loop through each worksheet
For Each ws In wb.Worksheets
  
      'Only if the worksheet name is not "MasterSheet"
      If ws.Name <> "MasterSheet" Then
       
        'Activate worksheet
        ws.Activate
        'Get the last row number
        numRows = Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count
        'Copy the range from A2 to the last row number of column I
        Range(Cells(2, "A"), Cells(numRows, "I")).Copy
        'Activate MasterSheet
        Worksheets("MasterSheet").Activate
        'Activate the next row of the last filled row in column A
        Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Activate
        'Paste as value
        ActiveCell.PasteSpecial (xlPasteValues)
          
      End If
          
Next ws
  
'Activate MasterSheet after all loops
Worksheets("MasterSheet").Activate
'Activate cell A1
Range("A1").Activate
  
End Sub 

The result of the code above looks like this:

Even if you don’t need this code right away, you may want to save it for the future, as you may encounter a task that requires you to combine different Excel worksheets into one.