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
- Add a new worksheet, and rename it “Collection”. You can also use other name.
- And in the new worksheet, input the headings of each column.
- Then press the shortcut key “Alt+F11”. And then the window of VBA will pop up.
- And double click the option of “ThisWorkbook” on the left of the interface.
- 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
- 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
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