Many office men may need to merge multiple Excel Worksheets into a single master worksheet if they want to analyze or count the data quickly and easily. Sometimes they could merge the worksheets manually, but if there are numerous worksheets, merging sheets manually would be time-consuming. Now I would introduce merging multiple Excel Worksheet to One Worksheet through VBA.
To merge data in Excel using this method, open your Excel files and, in the target Excel file for merging data, create a new worksheet by pressing the + (plus) button next to the sheet tabs at the bottom of the window. In your new worksheet, press Data Consolidate. A mailmerge can only reference a single data source, which in this case means a single worksheet. That said, if your workbook is structured such that there is a relationship between a 'master' sheet that you could use as the data source and the other worksheets, you might be able to use a DATABASE field in conjunction with the mailmerge to extract the data from those other sheets.
Now we suppose that you have three Worksheets contains some information about the students and now you would like to merge them into a Worksheet. Please follow the under steps if you are interested in learning merging multiple Excel Worksheet to one Worksheet through VBA.
Step 1: Press the ALT + F11 keys at same time to open the Microsoft Visual Basic for Applications window.
Step 2: Click Insert>>Module, then paste the under codes into the newly opened module.
Dim Sun As Integer
On Error Resume Next
Sheets(1).Name = 'Combined'
For Sun = 2 To Sheets.Count
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
Attention: If your sheet contains numerous columns of data, just set the Range in the last part of the code as big as possible.
Compare And Merge Excel Spreadsheets
Merge Excel Sheets Into One Workbook
Step 3: Press F5 or click the run icon in the toolbar. Then you would see there is a new sheet called Combined appearing in the Excel sheet line.
Merge Excel Sheets Online