Option Explicit
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
With Sheets("tri 3E").[A1].CurrentRegion
If Application.CountIf(.Columns(1), Sh.Name) = 0 Then Exit Sub
Application.ScreenUpdating = False
Sh.Cells.Delete 'RAZ
.AutoFilter 1, Sh.Name 'filtre automatique
.Copy Sh.[A1] 'copier-coller
.AutoFilter
End With
Sh.Columns.AutoFit 'ajustement largeurs
End Sub
Sub calculheterog()
'
' calculheterog Macro
'
'
With Worksheets("récapitulatif")
.Range("D3:D4").FormulaR1C1 = "=SUM('3e3'!R[-1]C[2]:R[26]C[2])"
.Range("E3:E4").FormulaR1C1 = "=SUM('3e4'!R[-1]C[1]:R[26]C[1])"
.Range("F3:F4").FormulaR1C1 = "=SUM('3e5'!R[-1]C:R[26]C)"
.Range("G3:G4").FormulaR1C1 = "=SUM('3e6'!R[-1]C[-1]:R[26]C[-1])"
.Range("H3:H4").FormulaR1C1 = "=SUM('3e7'!R[-1]C[-2]:R[26]C[-2])"
.Range("I3:I4").FormulaR1C1 = "=SUM('3e8'!R[-1]C[-2]:R[26]C[-3])"
.Range("J3:J4").FormulaR1C1 = "=SUM('repartition 2018 2019'!R[-1]C[-4]:R[160]C[-5])/7"
.Range("J3:J4").FormulaR1C1 = "=SUM('repartition 2018 2019'!R[-1]C[-5]:R[160]C[-5])/6"
End With
End Sub
Sub CALCULSEX()
'
' CALCULSEX Macro
'
'
With Worksheets("récapitulatif")
.Range("D6").FormulaR1C1 = "=COUNTIF('3e3'!R[-4]C[1]:R[23]C[1],""M"")"
.Range("D7").FormulaR1C1 = "=COUNTIF('3e3'!R[-5]C[1]:R[23]C[1],""F"")"
.Range("E6").FormulaR1C1 = "=COUNTIF('3e4'!R[-4]C:R[23]C,""M"")"
.Range("E7").FormulaR1C1 = "=COUNTIF('3e4'!R[-5]C:R[23]C,""F"")"
.Range("F6").FormulaR1C1 = "=COUNTIF('3e5'!R[-4]C[-1]:R[23]C[-1],""M"")"
.Range("F7").FormulaR1C1 = "=COUNTIF('3e5'!R[-5]C[-1]:R[23]C[-1],""F"")"
.Range("G6").FormulaR1C1 = "=COUNTIF('3e6'!R[-4]C[-2]:R[23]C[-2],""M"")"
.Range("G7").FormulaR1C1 = "=COUNTIF('3e6'!R[-5]C[-2]:R[23]C[-2],""F"")"
.Range("H6").FormulaR1C1 = "=COUNTIF('3e7'!R[-4]C[-3]:R[23]C[-3],""M"")"
.Range("H7").FormulaR1C1 = "=COUNTIF('3e7'!R[-5]C[-3]:R[23]C[-3],""F"")"
.Range("I6").FormulaR1C1 = "=COUNTIF('3e8'!R[-4]C[-3]:R[23]C[-4],""M"")"
.Range("I7").FormulaR1C1 = "=COUNTIF('3e8'!R[-5]C[-3]:R[23]C[-4],""F"")"
End With
End Sub