Merging Data From Multiple Sheets

[somnath6309]

I have recently read an article from the webpage Ron de Bruin, Microsoft Office Excel MVP regarding Merging data from different sheets. I have not under stood some expressions regarding which I required some detailed clarifications. I have given the full code with the attached file with highlighted question areas for the purpose of convenience of the solution provider. I have put here my questions in Green Color so that the reader can understand my question. Kindly provide answers if possible.

Function LastRow(sh As Worksheet)
OnErrorResumeNext

LookIn:=xlFormulas, _ why looking in Excel Formulas ?
SearchDirection:=xlPrevious, _ Why direction is xlprevious ?
MatchCase:=False).Row The total line is not understandable.
OnErrorGoTo 0 Why we use on error go to o Statement ?
.ScreenUpdating = False why these two lines have been used ?
.EnableEvents = False
EndWith

DestSh.Cells(Last + 1, "H").Resize(CopyRng.Rows.Count).Value = sh.Name This is not understandable to me

 

[Smallman]

Hi Somnath

I have always wondered why Ron doesn't just update his site. He has probably moved onto other things. There is some useful insights on his site the code you have plucked from the site is probably not his best work though. There are far easier and more efficient ways to consolidate a file than the method Ron is using.

Instead of going into Ron's code I will go one step further and give you some code which is a lot easier to get your head around. I will include some lines I don't usually use on code which runs efficiently. I have commented as much as I can.

Option Explicit 'Variable Declaration (a must on all vba modules)
 
Sub Consolidate()
Dim ws As Worksheet ' Declare ws as a worksheet for later use.
 
Application.ScreenUpdating = False ' Turn off Screen updating (Makes the code run faster, stop screen flicker)
For Each ws In ThisWorkbook.Worksheets 'Set the loop up.
    If ws.Name <> "Consol" Then 'Exclude the sheet you are consolidating to.
        ws.Range("A2", ws.Range("L65536").End(xlUp)).Copy Sheet1.Range("A65536").End(xlUp)(2)
    End If ' The above line is the executible line.  Template assumes Cols A to L.
Next ws
Application.ScreenUpdating = False ' Turn on screen updating.
End Sub

If all of this code is not something you can decipher I suggest starting with a good book. My first book was John Walkenback's Excel Power Programming with VBA.

I will post a workbook to show workings. Come back if you have any questions. There are plenty of people in here who are happy to assist.

Take it easy

Smallman