This article draws on the post from the site which is dedicated to merging worksheets in a workbook into a single master sheet Consolidate Sheets. This technique can be significantly simplified with a VBA macro. I have posted this type of reply on forums dozens of times. Howeve this reply differs just slightly. The following will consolidate data so the data tables appear side by side in the consolidaiton tab. So if Sheet 1 appears:
Jan Feb Mar
for the column headers. While sheet2 has;
Apr May Jun
The consolidation of these two is;
Jan Feb Mar Apr May Jun
In order to do this successfully you need to check the data in Row 1 for the last used column as this may vary.
If there are 4 sheets in your workbook and your data is laid out as follows:
The code when the sheets to consolidate are in blue is as follows. This can be run from any sheet and assumes the consolidation sheet index number is sheet4. If you run this code on a fresh worksheet and add a sheet it will run nicely.
Option Explicit
Sub MergeMe() 'Excel VBA to Merge worksheet columns
Dim i As Integer
For i = 1 To Worksheets.Count - 1
Sheets(i).[a1].CurrentRegion.Copy Sheet4.Range("IV1").End(xlToLeft)(, 2)
Next i
End Sub
The above assumes that your data is tabular and starts in A1 with headers. If you wanted to stack your data the following would achieve this.
Option Explicit
Sub MergeMe2() 'Excel VBA to Merge worksheet columns
Dim i As Integer
For i = 1 To Worksheets.Count - 1
Sheets(i).[A1].CurrentRegion.Offset(1, 0).Copy Sheet4.[A500000].End(xlUp)(2)
Next i
End Sub
Be careful as the above assumes 500K rows so if your data is greater than this it won't pick up all of your data. The above also assumes you have a header row ready and waiting in your consolidation sheet.
Hope this coding helps you merge your worksheets.
