Consolidate Sheets without headers

[undercrisis01]

Hello,

I tried running this code to consolidate all sheets into one master sheet
but i ran into this error

Sub or function not defined

it pointed out to this function.."LastRow" as the culprit

 For Each sh In ActiveWorkbook.Worksheets
        If sh.Name <> DestSh.Name Then
            ' Find the last row with data on the summary
            ' and source worksheets.
            Last = LastRow(DestSh)
            shLast = LastRow(sh)

This is the whole code

Sub CopyDataWithoutHeaders()
    Dim sh As Worksheet
    Dim DestSh As Worksheet
    Dim Last As Long
    Dim shLast As Long
    Dim CopyRng As Range
    Dim StartRow As Long
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    ' Delete the summary sheet if it exists.
    Application.DisplayAlerts = False
    On Error Resume Next
    ActiveWorkbook.Worksheets("RDBMergeSheet").Delete
    On Error GoTo 0
    Application.DisplayAlerts = True
    ' Add a new summary worksheet.
    Set DestSh = ActiveWorkbook.Worksheets.Add
    DestSh.Name = "RDBMergeSheet"
    ' Fill in the start row.
    StartRow = 2
    ' Loop through all worksheets and copy the data to the
    ' summary worksheet.
    For Each sh In ActiveWorkbook.Worksheets
        If sh.Name <> DestSh.Name Then
            ' Find the last row with data on the summary
            ' and source worksheets.
            Last = LastRow(DestSh)
            shLast = LastRow(sh)
            ' If source worksheet is not empty and if the last
            ' row >= StartRow, copy the range.
            If shLast > 0 And shLast >= StartRow Then
                'Set the range that you want to copy
                Set CopyRng = sh.Range(sh.Rows(StartRow), sh.Rows(shLast))
               ' Test to see whether there are enough rows in the summary
               ' worksheet to copy all the data.
                If Last + CopyRng.Rows.Count > DestSh.Rows.Count Then
                   MsgBox "There are not enough rows in the " & _
                   "summary worksheet to place the data."
                   GoTo ExitTheSub
                End If
                ' This statement copies values and formats.
                CopyRng.Copy
                With DestSh.Cells(Last + 1, "A")
                    .PasteSpecial xlPasteValues
                    .PasteSpecial xlPasteFormats
                    Application.CutCopyMode = False
                End With
            End If
        End If
    Next
ExitTheSub:
    Application.Goto DestSh.Cells(1)
    ' AutoFit the column width in the summary sheet.
    DestSh.Columns.AutoFit
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub

How to address this problem?

 

[VoG]

You need to add

Function LastRow(sh As Worksheet)
    On Error Resume Next
    LastRow = sh.Cells.Find(What:="*", _
                            After:=sh.Range("A1"), _
                            Lookat:=xlPart, _
                            LookIn:=xlFormulas, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).Row
    On Error GoTo 0
End Function

From http://msdn.microsoft.com/en-us/library/cc793964.aspx

 

[undercrisis01]

Thanks for the response.

can you tell me where in the code i should insert this function?
im not really versed with this kind of stuff.

 

[VoG]

Add it after your existing End Sub (i.e. at the bottom).

 

[undercrisis01]

Oh thanks man.. now its functional..

 

[tsharma727]

You need to add

Function LastRow(sh As Worksheet)
    On Error Resume Next
    LastRow = sh.Cells.Find(What:="*", _
                            After:=sh.Range("A1"), _
                            Lookat:=xlPart, _
                            LookIn:=xlFormulas, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).Row
    On Error GoTo 0
End Function

From Consolidating Data from Multiple Worksheets into a Summary Worksheet in Excel

Hi Peter,

I did get what I was looking and data is coming in my master sheet but I want to have header which is same in all the sheets and don't want repetition.

Regards,
Tarun