Macro pour consolider des valeurs de plusieurs onglet

Sub conso()
Application.ScreenUpdating = False
Set recap = Sheets("récap")
For f = 1 To Sheets.Count - 1
    nbcol = Application.Match("Total", Sheets(f).[A5:AZ5], 0)
    recap.Cells(Rows.Count, 1).End(xlUp)(2).Resize(nbcol - 2, 1) = Sheets(f).[B2]
    recap.Cells(Rows.Count, 2).End(xlUp)(2).Resize(nbcol - 2, 4) = Application.Transpose(Sheets(f).[B5].Resize(4, nbcol - 2))
    For col = 2 To nbcol - 1
        recap.Cells(Rows.Count, 6).End(xlUp)(2) = Application.Sum(Sheets(f).Cells(9, col).Resize(5, 1))
    Next col
Next f
Application.ScreenUpdating = True
End Sub

Sub Transpose1()
Dim ws As Worksheet, n As Long, x As Byte
    Application.ScreenUpdating = False
    Sheets("récap").Cells.Clear
    n = 2
    For Each ws In Worksheets
        If ws.Name <> "récap" Then
            With ws.Range("a5").CurrentRegion
                x = .Columns.Count - 2
                With .Offset(, 1).Resize(, x)
                    .Copy
                End With
            End With
            With Sheets("récap")
                .Cells(n, 1).Resize(x).Value = ws.Range("B2").Value
                .Cells(n, 2).PasteSpecial Paste:=xlPasteValues, Transpose:=True
                n = .Range("A" & Rows.Count).End(xlUp).Row + 1
            End With
        End If
    Next
    With Sheets("récap").Cells(1)
        .Resize(, 10).Value = [{"Dates","Secteurs","Total","Présents","Dispo","Bloqués A","Bloqués B","Bloqués C","Réservés 24h<","Réservés >24h"}]
        With .CurrentRegion
            With .Rows(1)
                .Interior.ColorIndex = 44
                .BorderAround Weight:=xlThin
            End With
            .Font.Name = "calibri"
            .VerticalAlignment = xlCenter
            .HorizontalAlignment = xlCenter
            .Borders(xlInsideVertical).Weight = xlThin
            .BorderAround Weight:=xlThin
            '.Columns.AutoFit
        End With
    End With
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

recap.[A2].Resize(recap.UsedRange.Rows.Count, 6).ClearContents

Sub conso()
Application.ScreenUpdating = False
Set recap = Sheets("récap")
recap.[A2].Resize(recap.UsedRange.Rows.Count, 6).ClearContents
For f = 1 To Sheets.Count - 1
    nbcol = Application.Match("Total", Sheets(f).[A5:AZ5], 0)
    recap.Cells(Rows.Count, 1).End(xlUp)(2).Resize(nbcol - 2, 1) = Sheets(f).[B2]
    recap.Cells(Rows.Count, 2).End(xlUp)(2).Resize(nbcol - 2, 4) = Application.Transpose(Sheets(f).[B5].Resize(4, nbcol - 2))
    For col = 2 To nbcol - 1
        recap.Cells(Rows.Count, 6).End(xlUp)(2) = Application.Sum(Sheets(f).Cells(9, col).Resize(5, 1))
    Next col
Next f
Application.ScreenUpdating = True
End Sub