Option Explicit
Dim Ligne As Long
'Form_Range = plage cellule Formulaire
'Tab_Range = plage cellule Tableau report
Dim Form_Range1 As Range, Tab_Range1 As Range
Dim Form_Range2 As Range, Tab_Range2 As Range
Dim Form_Range3 As Range, Tab_Range3 As Range
Dim Form_Range4 As Range, Tab_Range4 As Range
Dim Form_Range5 As Range, Tab_Range5 As Range
Dim Form_Range6 As Range, Tab_Range6 As Range
Dim Form_Range7 As Range, Tab_Range7 As Range
Dim Form_Range8 As Range, Tab_Range8 As Range
Dim Form_Range9 As Range, Tab_Range9 As Range
Dim Form_Range10 As Range, Tab_Range10 As Range
Dim Form_Range11 As Range, Tab_Range11 As Range
Dim Form_Range12 As Range, Tab_Range12 As Range
Dim Form_Range13 As Range, Tab_Range13 As Range
Dim Form_Range14 As Range, Tab_Range14 As Range
Dim decalage_ligne As Integer
'...
Private Sub Variable_config()
'ENC_ESP_AKS FR
Set Form_Range1 = Me.Range("C7:G7")
Set Tab_Range1 = Feuil3.[B:F]
'ENC_ESP_AKS CH
Set Form_Range2 = Me.Range("H7:L7")
Set Tab_Range2 = Feuil3.[I:M]
'TRANS_CB_CA_FR
Set Form_Range3 = Me.Range("C12:E12")
Set Tab_Range3 = Feuil4.[C:E]
Set Form_Range4 = Me.Range("F12:G12")
Set Tab_Range4 = Feuil4.[H:I]
Set Form_Range5 = Me.Range("H12:J12")
Set Tab_Range5 = Feuil4.[L:N]
Set Form_Range6 = Me.Range("K12:L12")
Set Tab_Range6 = Feuil4.[Q:R]
'TRANS_CB_CA_CH
Set Form_Range7 = Me.Range("C17:J17")
Set Tab_Range7 = Feuil7.[C:J]
'TRANS_CB_CA_CH Nbr
Set Form_Range8 = Me.Range("C18:J18")
Set Tab_Range8 = Feuil7.[L:S]
'ENC_ESP_DG4
Set Form_Range9 = Me.Range("C23:F23")
Set Tab_Range9 = Feuil5.[C:F]
Set Form_Range10 = Me.Range("G23:I23")
Set Tab_Range10 = Feuil5.[I:K]
'ENC_CM_FR
Set Form_Range11 = Me.Range("C29:F29")
Set Tab_Range11 = Feuil6.[C:F]
'ENC_CM_FR Nbr
Set Form_Range12 = Me.Range("C30:F30")
Set Tab_Range12 = Feuil6.[S:V]
'ENC_CM_CH
Set Form_Range13 = Me.Range("C34:L34")
Set Tab_Range13 = Feuil6.[H:Q]
'ENC_CM_CH Nbr
Set Form_Range14 = Me.Range("C35:K35")
Set Tab_Range14 = Feuil6.[X:AF]
'decalage 1ere ligne tableau
decalage_ligne = "3"
End Sub
Private Sub Worksheet_Activate()
Variable_config
Récupérerligne
End Sub
Private Sub Worksheet_Deactivate()
Variable_config
ReporterLigne
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Variable_config
'If Ligne = 0 Then MsgBox "calcul ligne"
If Ligne = 0 Then Ligne = Me.[C2].Value - [debut_exercice].Value + decalage_ligne ': Variable_config
If Target.Address = "$C$2" Then ReporterLigne: Récupérerligne
'OK_If Target.Address = "$C$7" Then MsgBox "change": Me.[C7:L7].Copy Destination:=Feuil11.[B:K].Rows(Ligne): Me.[C7:L7].Copy Destination:=Feuil12.[B:K].Rows(Ligne - 33)
'OK_If Not Intersect(Target, Range("C7:L7")) Is Nothing Then Me.[C7:L7].Copy Destination:=Feuil11.[B:K].Rows(Ligne)
If Not Intersect(Target, Form_Range1) Is Nothing Then Form_Range1.Copy Destination:=Tab_Range1.Rows(Ligne)
If Not Intersect(Target, Form_Range2) Is Nothing Then Form_Range2.Copy Destination:=Tab_Range2.Rows(Ligne)
If Not Intersect(Target, Form_Range3) Is Nothing Then Form_Range3.Copy Destination:=Tab_Range3.Rows(Ligne)
If Not Intersect(Target, Form_Range4) Is Nothing Then Form_Range4.Copy Destination:=Tab_Range4.Rows(Ligne)
If Not Intersect(Target, Form_Range5) Is Nothing Then Form_Range5.Copy Destination:=Tab_Range5.Rows(Ligne)
If Not Intersect(Target, Form_Range6) Is Nothing Then Form_Range6.Copy Destination:=Tab_Range6.Rows(Ligne)
If Not Intersect(Target, Form_Range7) Is Nothing Then Form_Range7.Copy Destination:=Tab_Range7.Rows(Ligne)
If Not Intersect(Target, Form_Range8) Is Nothing Then Form_Range8.Copy Destination:=Tab_Range8.Rows(Ligne)
If Not Intersect(Target, Form_Range9) Is Nothing Then Form_Range9.Copy Destination:=Tab_Range9.Rows(Ligne)
If Not Intersect(Target, Form_Range10) Is Nothing Then Form_Range10.Copy Destination:=Tab_Range10.Rows(Ligne)
If Not Intersect(Target, Form_Range11) Is Nothing Then Form_Range11.Copy Destination:=Tab_Range11.Rows(Ligne)
If Not Intersect(Target, Form_Range12) Is Nothing Then Form_Range12.Copy Destination:=Tab_Range12.Rows(Ligne)
If Not Intersect(Target, Form_Range13) Is Nothing Then Form_Range13.Copy Destination:=Tab_Range13.Rows(Ligne)
If Not Intersect(Target, Form_Range14) Is Nothing Then Form_Range14.Copy Destination:=Tab_Range14.Rows(Ligne)
End Sub
Private Sub Récupérerligne()
Ligne = Me.[C2].Value - [debut_exercice].Value + decalage_ligne
Application.EnableEvents = False
'Feuil11.[B:K].Rows(Ligne).Copy Destination:=Me.[C7:L7]
Tab_Range1.Rows(Ligne).Copy Destination:=Form_Range1
Tab_Range2.Rows(Ligne).Copy Destination:=Form_Range2
Tab_Range3.Rows(Ligne).Copy Destination:=Form_Range3
Tab_Range4.Rows(Ligne).Copy Destination:=Form_Range4
Tab_Range5.Rows(Ligne).Copy Destination:=Form_Range5
Tab_Range6.Rows(Ligne).Copy Destination:=Form_Range6
Tab_Range7.Rows(Ligne).Copy Destination:=Form_Range7
Tab_Range8.Rows(Ligne).Copy Destination:=Form_Range8
Tab_Range9.Rows(Ligne).Copy Destination:=Form_Range9
Tab_Range10.Rows(Ligne).Copy Destination:=Form_Range10
Tab_Range11.Rows(Ligne).Copy Destination:=Form_Range11
Tab_Range12.Rows(Ligne).Copy Destination:=Form_Range12
Tab_Range13.Rows(Ligne).Copy Destination:=Form_Range13
Tab_Range14.Rows(Ligne).Copy Destination:=Form_Range14
Application.EnableEvents = True
'MsgBox "recupererligne"
End Sub
Private Sub ReporterLigne()
'If Ligne <> 0 Then Me.[C7:L7].Copy Destination:=Feuil11.[B:K].Rows(Ligne)
If Ligne <> 0 Then Form_Range1.Copy Destination:=Tab_Range1.Rows(Ligne)
If Ligne <> 0 Then Form_Range2.Copy Destination:=Tab_Range2.Rows(Ligne)
If Ligne <> 0 Then Form_Range3.Copy Destination:=Tab_Range3.Rows(Ligne)
If Ligne <> 0 Then Form_Range4.Copy Destination:=Tab_Range4.Rows(Ligne)
If Ligne <> 0 Then Form_Range5.Copy Destination:=Tab_Range5.Rows(Ligne)
If Ligne <> 0 Then Form_Range6.Copy Destination:=Tab_Range6.Rows(Ligne)
If Ligne <> 0 Then Form_Range7.Copy Destination:=Tab_Range7.Rows(Ligne)
If Ligne <> 0 Then Form_Range8.Copy Destination:=Tab_Range8.Rows(Ligne)
If Ligne <> 0 Then Form_Range9.Copy Destination:=Tab_Range9.Rows(Ligne)
If Ligne <> 0 Then Form_Range10.Copy Destination:=Tab_Range10.Rows(Ligne)
If Ligne <> 0 Then Form_Range11.Copy Destination:=Tab_Range11.Rows(Ligne)
If Ligne <> 0 Then Form_Range12.Copy Destination:=Tab_Range12.Rows(Ligne)
If Ligne <> 0 Then Form_Range13.Copy Destination:=Tab_Range13.Rows(Ligne)
If Ligne <> 0 Then Form_Range14.Copy Destination:=Tab_Range14.Rows(Ligne)
'MsgBox "reporterligne"
End Sub