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.The 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.Input Heading into New Worksheet
  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.Double Click to Open ThisWorkbook
  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

  1. And then click the button “Run Sub”.Click Run Sub

And then come back to the worksheet, you will see the result.The Result of Collection

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

One response to “How to Merge Multiple Worksheets through Excel VBA”

  1. This program did not work on my computer as written. It did not seem to Id the “collection” workbook correctly (strDstSheet). is there something that needs to be changed to allow this to work as is.

    I made 3 changes to allow to this to work without having to go into the VBA again and again. however, it requires changing the location of the collection sheet to first index (so this would always need to stay in first position

    1. CHANGE THE FOLLOWING LINE OF CODE

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

    TO THIS

    Worksheets(“Collection”).Cells(nDstStartRowIndex, colIndex) = Worksheets(strSrcSheet).Cells(rwIndex, colIndex)

    2: REMOVED THE IF/THEN STATEMENT

    3: CHANGED THE SHEET INDEX RANGE STARTING POINT FROM 1 TO 2 AND THEN MOVED THE COLLECTION SHEET TO THE FRONT (MAKING IT SHEET 1).

    For nSheetIndex = 2 To Worksheets.Count