Defining "LastRow" in a worksheet - part of a VBA that aggregates other sheets

Question

I am rehashing an older file of mine and cannot figure out how to define Last row so that the script can properly find the last row of data in the destination worksheet and active worksheet.

When run I get the definition error. I'm sure this is a simple item.

Below is a copy of the VBA:

Sub Summarize_RC()

    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 old "summary" shet
    Application.DisplayAlerts = False

    On Error Resume Next

    ActiveWorkbook.Worksheets("Summary").Delete

    On Error GoTo 0

    Application.DisplayAlerts = True

    'make new "Summary" ws
    Set DestSh = ActiveWorkbook.Worksheets.Add
    DestSh.Name = "Summary"

    'define start row, should grow with more marketers
    StartRow = 10

    'loop through all worksheets and copy the data to the DestSh
    For Each sh In ActiveWorkbook.Worksheets

        'loop through all worksheets except the Summary worksheet and the
        '"Data" worksheet
        If IsError(Application.Match(sh.Name, _
                                     Array(DestSh.Name, "Valid"), 0)) Then

            'find the last row with data on the DestSh and sh
            Last = LastRow(DestSh)
            shLast = LastRow(sh)

            'if sh is not empty and if the last row >= StartRow copy the CopyRng
            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 if there enough rows in the DestSh to copy all the data
                If Last + CopyRng.Rows.Count > DestSh.Rows.Count Then
                    MsgBox "There are not enough rows in the Destsh"
                    GoTo ExitTheSub
                End If

                'copy over the values and formats (incase formulas from input or someshit
                CopyRng.Copy

                With DestSh.Cells(Last + 1, "A")

                    .PasteSpecial xlPasteValues
                    .PasteSpecial xlPasteFormats
                    Application.CutCopyMode = False

                End With

                'close out the two other IFs not previously closed
            End If

        End If
    Next

 'pray to greatbig spaghetti moster in the sky

ExitTheSub:

End Sub

Below is a copy of the function (this is where the error existed and has since been fixed):

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

Answer 1

Try the following syntax:

Instead of

shLast = LastRow(sh)

Which implies you are calling a function with a worksheet argument, returning a Long, and then assigning the result to your shLast variable; using the code in the comments (you posted), try:

shLast = sh.Cells.Find(what:="*", After:=sh.Range("A1"), Lookat:=xlPart, LookIn:=xlFormulas,SearchOrder:=xlByRows,SearchDirection:=xlPrevious, MatchCase:=False).Row 

Likewise:

Last = LastRow(DestSh)

Becomes:

Last = DestSh.Cells.Find(what:="*", After:=DestSh.Range("A1"), Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row 

As a function with a test call:

Private Sub TestLastRowFunc()

    Dim wb As Workbook
    Dim sh As Worksheet
    Dim DestSh As Worksheet

    Set wb = ThisWorkbook
    Set sh = wb.Worksheets("sh")
    Set DestSh = wb.Worksheets("DestSh")

    Dim Last As Long
    Dim shLast As Long

    Last = LastRow(sh)
    shLast = LastRow(DestSh)

    MsgBox Last
    MsgBox shLast

End Sub  

Private Function LastRow(ByRef mySheet As Worksheet) As Long

    LastRow = mySheet.Cells.Find(What:="*", After:=mySheet.Range("A1"), _ 
        LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, _ 
        SearchDirection:=xlPrevious, MatchCase:=False).Row

End Function