How to Merge Multiple Worksheets through Excel VBA

Excel is so popular among people mainly because it combines the function of VBA. Sometimes, a few lines of VBA can achieve a lot of functions. Thus, now we will introduce the VBA to merge multiple worksheets with the same format.

In your work, sometimes you need to ask many people some questions through the form of Excel. For example, suppose you are a teacher, and you have sent your students the Excel files. And in this file, students need to make choices on different tasks. And now you have input the worksheet into one Excel file. In addition, the formats are the same except for the detail information and students’ choices.

However, you still need to input the information into one worksheet. Besides, if you copy and paste one by one, it will require a lot of time. Therefore, here you can use the VBA code to help you finish this work.

Summarize Worksheet through VBA

1. Add a new worksheet, and rename it “Collection”. You can also use other name.
2. And in the new worksheet, input the headings of each column.
3. Then press the shortcut key “Alt+F11”. And then the window of VBA will pop up.
4. And double click the option of “ThisWorkbook” on the left of the interface.
5. Then, in the new window, input the VBA code into it.

Function CopyOneSheetData(ByVal strSrcSheet As String, ByVal strDstSheet As String, ByRef nDstStartRowIndex As Integer)

Dim rwIndex As Integer

Dim colIndex As Integer

For rwIndex = 2 To Worksheets(strSrcSheet).UsedRange.Rows.Count

    For colIndex = 1 To Worksheets(strSrcSheet).UsedRange.Columns.Count

        Worksheets(strDstSheet).Cells(nDstStartRowIndex, colIndex) = Worksheets(strSrcSheet).Cells(rwIndex, colIndex)

    Next colIndex

    nDstStartRowIndex = nDstStartRowIndex + 1

Next rwIndex

 End Function

Sub CopyAllSheets()

Dim nDstStartRowIndex As Integer

Dim nSheetIndex As Integer

nDstStartRowIndex = 2

 For nSheetIndex = 1 To Worksheets.Count

    If Worksheets(nSheetIndex).Name <> “Collection” Then

        Call CopyOneSheetData(Worksheets(nSheetIndex).Name, “Collection”, nDstStartRowIndex)

    End If

 Next nSheetIndex

End Sub

6. And then click the button “Run Sub”.

And then come back to the worksheet, you will see the result.

Thus, if you need to collect hundreds or even thousands of Excel worksheets into one sheet, you can also use this VBA code. And it is very effective in handling with such cases.

When You Meet with Damaged Excel Files

With the development of technology, there are more and more computer virus and malware on the internet. Therefore, you will unavoidably meet with one and unfortunately your Excel files will corrupt. Thus, the third party software can help you fix Excel and retrieve your important information contained in the damaged files.

Author Introduction:

Anna Ma is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including word recovery and outlook repair software products. For more information visit www.datanumen.com