Macro pour la plus grande valeur dans une feuille excel

Sub consolide()

ChDir ActiveWorkbook.Path
Set recap_DELAM = ActiveWorkbook

Application.ScreenUpdating = False
compteur = 3

nf = Dir("*.xls")
Do While nf <> ""
    If nf <> recap_DELAM.Name Then
        Workbooks.Open Filename:=nf
        Set WBOpened = ActiveWorkbook
        With WBOpened
            With .Sheets("Synthèse")
                recap_DELAM.Sheets(1).Cells(compteur, 1) = .Range("CS4").Value
                recap_DELAM.Sheets(1).Cells(compteur, 2) = .Range("Q12").Value
                recap_DELAM.Sheets(1).Cells(compteur, 3) = .Range("CY4").Value
                recap_DELAM.Sheets(1).Cells(compteur, 4) = .Range("CI40").Value
                recap_DELAM.Sheets(1).Cells(compteur, 5) = .Range("CY40").Value
                recap_DELAM.Sheets(1).Cells(compteur, 6) = .Range("AA40").Value
                recap_DELAM.Sheets(1).Cells(compteur, 7) = .Range("BE40").Value
                recap_DELAM.Sheets(1).Cells(compteur, 8) = .Range("CI55").Value
                recap_DELAM.Sheets(1).Cells(compteur, 7) = .Range("CM1").Value
                recap_DELAM.Sheets(1).Cells(compteur, 10) = .Range("CM2").Value
            End With
            With .Sheets("Débit Horaire (C)")
                .Activate
                recap_DELAM.Sheets(1).Cells(compteur, 13) = Application.WorksheetFunction.Max(Range("L10:$BS33"))
            End With
        End With
        compteur = compteur + 1
        Workbooks(nf).Close False
    End If
    nf = Dir
Loop
Application.ScreenUpdating = True
End Sub

Sub MaxVal()
Dim plage As Range, ValMax As Long
    With Sheets("Feuil1")
        Set plage = .Range("L10:BY" & .Range("BY" & .Rows.Count).End(xlUp).Row)
    End With
    ValMax = WorksheetFunction.Max(Plage)
    MsgBox "la valeur max est " & ValMax
End Sub