Option Explicit
Sub test()
Dim a, b(), i As Long, n As Long, t As Long, ecole As String, secteur As String
Dim ws As Worksheet, dico1 As Object, dico2 As Object
Set dico1 = CreateObject("Scripting.Dictionary")
dico1.CompareMode = 1
Set dico2 = CreateObject("Scripting.Dictionary")
dico2.CompareMode = 1
t = 3
For Each ws In Worksheets
If ws.Name <> "EFFECTIFS ENSEIGNANTS" Then
If Not dico2.exists(ws.Range("g2").Value) Then
t = t + 1
dico2(ws.Range("g2").Value) = t
End If
End If
Next
dico1("Maternelle") = 4: dico1("Élémentaire") = 5
ReDim b(1 To dico1.Count + 4, 1 To dico2.Count + 4)
b(1, 1) = "ÉCOLES PUBLIQUES": b(2, 1) = "Nombre d'enseignants"
b(2, 3) = "Soit": b(2, 4) = "SECTEUR": b(2, UBound(b, 2)) = "Hors REP+"
b(4, 1) = "Maternelle :": b(5, 1) = "Élémentaire :": b(6, 1) = "Total"
For Each ws In Worksheets
If ws.Name <> "EFFECTIFS ENSEIGNANTS" Then
With ws.Range("a4").CurrentRegion
With .Offset(1).Resize(.Rows.Count - 2)
a = .Value
End With
End With
ecole = ws.Range("g1").Value
secteur = ws.Range("g2").Value
b(3, dico2(secteur)) = secteur
n = 0
For i = 1 To UBound(a, 1)
If a(i, 1) <> "" Then n = n + 1
Next
b(dico1(ecole), dico2(secteur)) = b(dico1(ecole), dico2(secteur)) + n
End If
Next
For i = 4 To UBound(b, 1) - 1
b(i, 2) = Application.Sum(Application.Index(b, i, Evaluate("row(4:" & UBound(b, 2) - 1 & ")")))
b(i, UBound(b, 2)) = Application.Sum(Application.Index(b, i, Evaluate("row(5:" & UBound(b, 2) - 1 & ")")))
Next
b(UBound(b, 1), 2) = Application.Sum(Application.Index(b, Evaluate("row(4:" & UBound(b, 1) - 1 & ")"), 2))
For i = 4 To UBound(b, 1) - 1
If b(UBound(b, 1), 2) <> 0 Then
b(i, 3) = b(i, 2) / b(UBound(b, 1), 2)
End If
Next
For i = 3 To UBound(b, 2)
b(UBound(b, 1), i) = Application.Sum(Application.Index(b, Evaluate("row(4:" & UBound(b, 1) - 1 & ")"), i))
Next
Application.ScreenUpdating = False
'restitution et mise en forme
With Sheets("EFFECTIFS ENSEIGNANTS")
With .Range("i1").Resize(UBound(b, 1), UBound(b, 2))
.CurrentRegion.Clear
.Value = b
.Font.Name = "calibri"
.Font.Size = 11
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlCenter
.BorderAround Weight:=xlThin
.Borders(xlInsideVertical).Weight = xlThin
With .Columns(3)
.Offset(3).Resize(.Rows.Count - 3).NumberFormat = "0.00%"
End With
With .Rows(1)
.HorizontalAlignment = xlCenterAcrossSelection
.BorderAround Weight:=xlThin
.Interior.ColorIndex = 19
End With
.Rows(3).BorderAround Weight:=xlThin
With .Rows(2)
.BorderAround Weight:=xlThin
With .Cells(1).Resize(2, 2)
.Interior.ColorIndex = 44
.MergeCells = True
End With
.Cells(3).Resize(2).MergeCells = True
.Cells(4).Resize(, .Columns.Count - 4).MergeCells = True
.Cells(.Columns.Count).Resize(2).MergeCells = True
End With
With .Rows(.Rows.Count)
.BorderAround Weight:=xlThin
.Interior.ColorIndex = 43
End With
.Columns.ColumnWidth = 14
End With
.Activate
End With
Application.ScreenUpdating = True
End Sub