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 48
| 'Dim Club As String, Statut As String
'Dim N° As String, Sport As String, Discipline As String, Type_P As String, Contact As String, Fonction As String
'Dim Adr1 As String, Adr2 As String, Cp As String, Ville As String, Pays As String, Zone As String
'Dim Tel As String, Mail As String, Commercial As String
'Dim Pr As String, Cl As String
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Pr = ThisWorkbook.Name
If Target.Column = 16 And Target.Value <> "" Then
'N° = Target.Offset(0, -16).Value
Club = Target.Offset(0, -15).Value
Sport = Target.Offset(0, -14).Value
Discipline = Target.Offset(0, -13).Value
Type_P = Target.Offset(0, -12).Value
Contact = Target.Offset(0, -11).Value
Fonction = Target.Offset(0, -10).Value
Adr1 = Target.Offset(0, -9).Value
Adr2 = Target.Offset(0, -8).Value
Cp = Target.Offset(0, -7).Value
Ville = Target.Offset(0, -6).Value
Pays = Target.Offset(0, -5).Value
Zone = Target.Offset(0, -4).Value
Tel = Target.Offset(0, -3).Value
Mail = Target.Offset(0, -2).Value
Commercial = Target.Offset(0, -1).Value
Statut = Target.Value
Cl = Sheets("Emplacement_Fichier_SDD").Range("B2").Value
Recopie_des_infos
Windows(Pr).Activate
End If
End Sub
Sub Recopie_des_infos()
Dim f2 As Worksheet
Dim DerLig_f2 As Long
Dim i As Long
Windows(Cl).Activate
Set f2 = Sheets("SDD 21")
If Statut <> "Demande de devis" Then
Set c = f2.Columns(2).Find(Club, LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then f2.Cells(c.Row, "A").EntireRow.Delete
Else
DerLig_f2 = f2.Range("B" & Rows.Count).End(xlUp).Row
f2.Range(f2.Cells(DerLig_f2 + 1, "B"), f2.Cells(DerLig_f2 + 1, "D")) = Array(Club, Sport, Discipline)
f2.Range(f2.Cells(DerLig_f2 + 1, "F"), f2.Cells(DerLig_f2 + 1, "P")) = Array(Contact, Fonction, Adr2, Adr2, Cp, Ville, Pays, Zone, Tel, Mail, Commercial)
End If
End Sub |
Partager