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:
- creates a new sheet and calls it “MergeSheet”
- 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
- 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.SelectSelection.Copy Destination:=Sheets(1).Range(“A1048576”).End(xlUp)(2)
Next
End Sub
New post on how to make merging data from various worksheets quick and easy! #[Excel Community of Practice]
This is super helpful! Thank you for posting this. Question though…How can I tailor this macro to merge only rows of data from other worksheets that have been flagged. For example, I have 5 worksheets with 100 line items. I only want to merge into one sheet the line items from each worksheet that has been “flagged” with some identifier, such as an open issue. Would the steps you outlined still apply? Thank you!