Private Sub Worksheet_Activate()
Dim P As Range, i&, dat As Long, F As Worksheet, lig As Variant, col As Variant
Application.ScreenUpdating = False
On Error Resume Next 'si aucune SpecialCell
Set P = Range("A1", UsedRange)
P.Columns("H").SpecialCells(xlCellTypeConstants) = "" 'RAZ sans toucher aux formules
For i = 1 To P.Rows.Count
If IsDate(P(i, 2)) Then dat = P(i, 2): P(i + 1, "H") = "Commentaires" 'remet le titre
Set F = Sheets(IIf(P(i, 3) = "CP" Or P(i, 3) = "OM", "CP-OM", "OTCM-OMR"))
lig = Application.Match(P(i, 4), F.Columns(3), 0)
col = Application.Match(dat, F.Rows(IIf(F.Name = "CP-OM", 4, 3)), 0)
If IsNumeric(lig) And IsNumeric(col) Then _
If Not F.Cells(lig, col).Comment Is Nothing Then P(i, "H") = Replace(F.Cells(lig, col).Comment.Text, vbLf, " ")
Next
End Sub