Private Sub Worksheet_Change(ByVal Target As Range)
Dim chemin$, dat$, s As Shape, lig&, col%, at$, sp, vis%
Dim c As Range, flag As Boolean, r As Range, n%, ntrans&, ncourse&
If Intersect(Target, [A4]) Is Nothing Then Exit Sub
[A4].Select
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False 'si un fichier source est déjà ouvert
Application.CopyObjectsWithCells = True 'permet la copie des objets
Me.DrawingObjects.Delete: [B:G].Clear 'RAZ
If [A4] = "" Then GoTo 1
'---copie du fichier source---
chemin = ThisWorkbook.Path & "\" 'chemin des fichiers sources, à adapter
dat = Mid(ThisWorkbook.Name, 8, 7) & "-" & Format([A4], "00")
If Not IsDate(dat) Then [A4] = "": GoTo 1
If Dir(chemin & "Courses " & dat & ".xls") = "" Then MsgBox "Aucune course ce jour-là...": GoTo 1
With Workbooks.Open(chemin & "Courses " & dat & ".xls").Sheets(1)
.[B:D].Copy [B1]
.[A1].Copy .[A1] 'important : vide la mémoire
.Parent.Close False
End With
'---analyse des Shapes---
For Each s In Shapes
lig = s.TopLeftCell.Row
col = s.TopLeftCell.Column
at = s.AlternativeText
sp = Split(at, "_")
If at Like "hippodrome*" Then
Cells(lig + 2, 5).Resize(, 3).Interior.Color = 13434828
ElseIf UBound(sp) > 0 Then
If IsNumeric(sp(1)) Then
If col = 2 Then Cells(lig, 7) = sp(1)
If col = 3 Then Cells(lig, 6) = sp(1)
If col = 4 Then Cells(lig, 5) = sp(1)
End If
Cells(lig, 5).Resize(2, 3).Interior.Color = 10079487
End If
Next s
'---création et remplissage de la feuille des pronos---
On Error Resume Next
If IsError(Sheets(dat)) Then
With Sheets("Modele")
vis = .Visible 'la feuille peut être masquée
.Visible = xlSheetVisible
.Copy After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = dat
.Visible = vis
End With
End If
On Error GoTo 0
With Sheets(dat)
For Each c In .UsedRange
If c = "CHX" Then c(1, 2).Resize(, 6) = "" 'RAZ
Next c
For Each c In Intersect([E:G], Me.UsedRange.EntireRow)
If c.Interior.ColorIndex = xlNone Then
flag = False
ElseIf Not flag And c.Interior.Color = 13434828 Then
flag = True
sp = Split(c(-2, -2), "C") 'références de la course
Set r = Nothing
n = 0
If UBound(sp) > 0 Then
Set r = .Cells.Find(sp(0), , xlValues, xlWhole)
If Not r Is Nothing Then Set r = r.EntireColumn.Find("Course " & sp(1))
If Not r Is Nothing Then Set r = r(2, 2).Resize(, 6): ntrans = ntrans + 1
End If
ElseIf flag And Not r Is Nothing And IsNumeric(CStr(c)) Then
n = n + 1
If n < 7 Then r(n) = c
End If
Next c
[E:G].Clear
ncourse = Application.CountIf([C:C], "Hippodrome*")
.Activate
End With
1 Application.EnableEvents = True
Application.ScreenUpdating = True
If ncourse <> ntrans Then MsgBox ncourse & " courses, " & ntrans & " transférées..."
End Sub