Combining Multiple Sheets in Excel

While utilizing Excel software for document creation or data analysis, you will likely encounter the need to use multiple sheets to store data and later wish to merge data from several sheets into a single sheet for consolidation. In this article, Mytour will guide you on a swift method to merge multiple sheets into one.

Traditionally, the thought of merging multiple sheets into one in Excel might lead some to consider the manual approach of copying data from one sheet to another. However, as intelligent Excel users, we can employ tricks to merge multiple sheets without the need for manual data copying and pasting.

Using the example below with two data sheets, Mytour will demonstrate merging data from these two sheets into the Consolidated sheet at the end.

To merge sheets together, utilize Microsoft Visual Basic in Excel.

Use the key combination Alt F11 to open Microsoft Visual Basic. Then, click on the Insert tab on the Visual Basic toolbar and select Module.

Next, paste the following code snippet into the editor of Microsoft Visual Basic:

Sub MergeSheets()
  Const NHR = 1
  Dim MWS As Worksheet
  Dim AWS As Worksheet
  Dim FAR As Long
  Dim LR As Long
  Set AWS = ActiveSheet
  For Each MWS In ActiveWindow.SelectedSheets
  If Not MWS Is AWS Then
  FAR = AWS.UsedRange.Cells(AWS.UsedRange.Cells.Count).Row  + 1
  LR = MWS.UsedRange.Cells(MWS.UsedRange.Cells.Count).Row
  MWS.Range(MWS.Rows(NHR + 1), MWS.Rows(LR)).Copy  AWS.Rows(FAR)
  End If
  Next MWS
End Sub

Afterward, click on the main Excel sheet. Open the sheet where you want to consolidate other sheets. Hold down the Ctrl key and select the sheets you want to merge.

Then, click to open the View ribbon on the toolbar.

Select Macros and choose the View Macros option.

When the Macro dialog appears, you can see the MergeSheets entry in the macro run list. Select this entry and click Run to execute the command.

Your consolidated sheet will now contain data from the selected sheets.

This guide on merging multiple sheets into one sheet by Mytour concludes here. We hope that through this article, you have grasped the method of combining multiple sheets into one. See you in other articles on the page.