Summary sheet to refer to cell on multiple sheets (always same column, but row# can vary)

Hi Everybody,

I found some code from Ron de Bruin that creates a Summary sheet form all worksheets. Of course, it works perfectly, but I want to modify it slightly to meet my needs. In his example code, he has it referring to:

 For Each myCell In Sh.Range("A1,D5:E5,Z10")

I tried modifying to point to a different range, but it throws an error. Here is the modification I made:

 For Each myCell In Sh.Range("M2").End(xlDown).Offset(2, 0)

My goal with the above code is to refer to the cell two rows below the last cell with continuous data in column M on every sheet in the workbook.

Here is the entire sample code provided by Ron without modification:

 Sub Summary_All_Worksheets_With_Formulas()
    Dim Sh As Worksheet
    Dim Newsh As Worksheet
    Dim myCell As Range
    Dim ColNum As Integer
    Dim RwNum As Long
    Dim Basebook As Workbook

    With Application
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With

    'Delete the sheet "Summary-Sheet" if it exist
    Application.DisplayAlerts = False
    On Error Resume Next
    ThisWorkbook.Worksheets("Summary-Sheet").Delete
    On Error GoTo 0
    Application.DisplayAlerts = True

    'Add a worksheet with the name "Summary-Sheet"
    Set Basebook = ThisWorkbook
    Set Newsh = Basebook.Worksheets.Add
    Newsh.Name = "Summary-Sheet"

    'The links to the first sheet will start in row 2
    RwNum = 1

    For Each Sh In Basebook.Worksheets
        If Sh.Name <> Newsh.Name And Sh.Visible Then
            ColNum = 1
            RwNum = RwNum + 1
            'Copy the sheet name in the A column
            Newsh.Cells(RwNum, 1).Value = Sh.Name

            For Each myCell In Sh.Range("A1,D5:E5,Z10")  '<--Change the range
                ColNum = ColNum + 1
                Newsh.Cells(RwNum, ColNum).Formula = _
                "='" & Sh.Name & "'!" & myCell.Address(False, False)
            Next myCell

        End If
    Next Sh

    Newsh.UsedRange.Columns.AutoFit

    With Application
        .Calculation = xlCalculationAutomatic
        .ScreenUpdating = True
    End With
End Sub

All help is greatly appreciated!

Carlos