Merge data from different work sheets into one sheet tab

When working with Excel sheets provided by other users, sometimes you find that users have separated similar data into multiple tabs due to difference of source, location, or some other identifier. The structure of the data tables is exactly the same, but the previous user felt it best to split the tables across tabs. If the reason for obtaining data was for analysis purposes, you may find the workbook challenging to work with as all the tables have to be brought into one tab to conduct the necessary analysis. The following code makes the process of merging data across tabs quick and seamless.

Before this macro can be deployed, it is important to confirm that all the tables have the same structure across sheets i.e. all tables start from cell A2, Column A is Date column in all sheets and Column K is Product code column in all sheets, and so on. If this is not the case, you may need to manually streamline the tables across the sheets before you run the code. Also all the tables must be perfectly contiguous, i.e. there must be no blank cells as the macro selects the full height and width of the table based on the assumption that there are no empty cells.

The macro code does the following:

1.  creates a new sheet and calls it "MergeSheet"
2. identifies the beginning of the table in the first sheet, identifies the full height and width of the table, copies the entire table and pastes into the newly created Merge Sheet
3. repeats (2) for all sheets in the workbook, noting the first empty cell in MergeSheet as the starting point so all the data comes together as one contiguous table.

Sub MergeSheets()

Dim M As Integer

On Error Resume Next
Sheets(1).Select
Worksheets.Add
Sheets(1).Name ="MergeSheet"

For M = 2 To Sheets.Count
Sheets(M). Activate
Range("A1").Select
Selection.CurrentRegion.Select

Selection.Copy Destination:=Sheets(1).Range("A1048576").End(xlUp)(2)

Next

End Sub