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 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47
| Sub Repartition()
Dim Ws1 As Worksheet, Ws2 As Worksheet, Ws3 As Worksheet
Dim c As Range
Dim Pays
Dim DerLig2 As Long
Dim Trouve As Boolean
Set Ws1 = Worksheets("Fiche de Travail J")
Pays = Array("FRANCE", "MAROC", "ESPAGNE")
'Répartition des données
For i = 0 To UBound(Pays)
Set c = Ws1.Cells.Find(Pays(i), LookIn:=xlValues)
If Not c Is Nothing Then
'Effacement de la feuille pays
If Trouve = False Then Worksheets(Pays(i)).Cells.Delete
Trouve = True
firstAddress = c.Address
Do
Set Ws2 = Worksheets(Pays(i))
DerLig2 = Ws2.Range("A" & Ws2.Rows.Count).End(xlUp).Row
If DerLig2 = 1 And Ws2.Range("A1") = "" Then DerLig2 = 0
'Colle les nouvelles données dans cette page
c.EntireRow.Copy Destination:=Ws2.Range("A" & DerLig2 + 1)
c.EntireRow.Font.ColorIndex = 5
Set c = Ws1.Cells.FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
If Trouve Then
'Tri des colonnes
Call TriColonne(Ws2)
Trouve = False
End If
Next i
Ws1.Select
Message = "Voulez-vous effacer la feuille travail ?"
Titre = "Fin de traitement"
Style = vbYesNo + vbCritical + vbDefaultButton2
Reponse = MsgBox(Message, Style, Titre)
If Reponse = vbYes Then
Cells.ClearContents
End If
Cells.Font.ColorIndex = 1
Range("A1").Select
Set c = Nothing
Set Ws1 = Nothing
Set Ws2 = Nothing
Set Ws3 = Nothing
End Sub |