1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31
| Sub MAJ()
Dim Chemin As String, Chemin_complet As String, Fic As String, Sini As String, Dossier As String
Dim Cible As Workbook, Source As Workbook
Dim FirstRow As Long, LastRow As Long, i As Long
Dim C As Range
Chemin = "P:\SINISTRES"
Fic = "Récapitulatif des sinistres.xlsm"
Chemin_complet = Chemin & "\" & Fic
Set Cible = Workbooks.Open(Chemin_complet)
Set Source = ThisWorkbook
Dossier = Source.Worksheets("Les_faits").Range("A9").Value
With Cible.Worksheets("Recap")
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
Set C = .Columns("A").Find(Dossier, , xlValues, xlWhole)
If Not C Is Nothing Then
C.Offset(0, 1).Value = Source.Worksheets("Résultat_enquête").Range("A2").Value
C.Offset(0, 2).Value = Source.Worksheets("Résultat_enquête").Range("D2").Value
C.Offset(0, 3).Value = Source.Worksheets("Suivi_courrier").Range("E6").Value
C.Offset(0, 4).Value = Source.Worksheets("Assurance").Range("M2").Value
Else
LastRow = Cible.Worksheets("Recap").Range("A" & Rows.Count).End(xlUp).Row
.Range("A" & LastRow + 1).Value = Source.Worksheets("Les_faits").Range("A9").Value
.Range("B" & LastRow + 1).Value = Source.Worksheets("Résultat_enquête").Range("A2").Value
.Range("C" & LastRow + 1).Value = Source.Worksheets("Résultat_enquête").Range("D2").Value
.Range("D" & LastRow + 1).Value = Source.Worksheets("Suivi_courrier").Range("E6").Value
.Range("E" & LastRow + 1).Value = Source.Worksheets("Assurance").Range("M2").Value
End If
End With
Cible.Close SaveChanges:=True
ThisWorkbook.Close SaveChanges:=True
End Sub |