Sub copiecolle()
'
Dim dernierecolonne As Long
Dim i As Long
Application.DisplayStatusBar = True
Application.ScreenUpdating = False
' compteur du temps d'exécution
Start = Timer
'Application.ScreenUpdating = False
' Feuil3 = TCD
Dim FTDC As Worksheet
Set FTDC = Worksheets("TCD")
dernierecolonne = FTDC.Range("G470").End(xlToRight).Column
Dim FCom As Worksheet
Set FCom = Worksheets("Com")
' Feuil5 = Com
' Stock dans une variable tableau
Dim tabFormul() As Variant
ReDim tabFormul(2 To FCom.Range("A65536").End(xlUp).Row, 1 To FTDC.Range("G470").End(xlToRight).Column)
For i = 2 To FCom.Range("A65536").End(xlUp).Row
' Feuil7 = E
Dim FE As Worksheet
Set FE = Worksheets("E")
' Sheets("E").Select
With FE.Range("K" & i)
.AutoFill Destination:=FE.Range("K" & i & ":L" & i), Type:=xlFillDefault
End With
FE.Range("L2:L" & i).Copy
With FE.Range(FE.Cells(2, 12), FE.Cells(i, dernierecolonne + 3))
.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End With
' Feuil11 = C
Dim FC As Worksheet
Set FC = Worksheets("C")
'Sheets("C").Select
With FC.Range("K" & i)
.AutoFill Destination:=FC.Range("K" & i & ":L" & i), Type:=xlFillDefault
End With
FC.Range("L2:L" & i).Copy
With FC.Range(FC.Cells(2, 12), FC.Cells(i, dernierecolonne + 3))
.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End With
' Feuil8 = R
Dim FR As Worksheet
Set FR = Worksheets("R")
'Sheets("R").Select
With FR.Range("K" & i)
.AutoFill Destination:=FR.Range("K" & i & ":L" & i), Type:=xlFillDefault
End With
FR.Range("L2:L" & i).Copy
With FR.Range(FR.Cells(2, 12), FR.Cells(i, dernierecolonne + 3))
.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End With
Application.StatusBar = Timer - Start & " secondes" & " - reste Lignes à traiter = " & FCom.Range("A65536").End(xlUp).Row - i
Next i
MsgBox "durée du traitement: " & Timer - Start & " secondes"
Application.DisplayStatusBar = False
Application.ScreenUpdating = True
End Sub