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)
nDstStartRowIndex = nDstStartRowIndex + 1
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)
- 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.
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