Bonjour
voici mon code
sub flk
Sheets("NON PAYEE TOTAL").Range("I3").Select
Selection.Copy
Range("J3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:úlse, Transpose:úlse
Sheets("NON PAYEE TOTAL").Range("I3") = ""
For I = Range("c65536").End(xlUp).Row To 7 Step -1
Cells(I, 1).EntireRow.Clear ' le 2 correspond au numero de colonne
Next
ligne = 2
With Sheets("2007")
For I = 1 To .[a65000].End(xlUp).Row
If .Cells(I, "P") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 13).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 17).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 25).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
With Sheets("2008")
For I = 1 To .[a65000].End(xlUp).Row
If .Cells(I, "Q") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 21).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 26).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
.Cells(I, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 11)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
With Sheets("2009")
For I = 1 To .[a65000].End(xlUp).Row
'MsgBox (.Cells(I, "Q"))
If .Cells(I, "Q") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 21).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 26).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
.Cells(I, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 11)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
With Sheets("2010")
For I = 1 To .[a65000].End(xlUp).Row
If .Cells(I, "Q") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 21).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
.Cells(I, 28).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 11)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
With Sheets("2011")
For I = 1 To .[a65000].End(xlUp).Row
If .Cells(I, "Q") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 21).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
.Cells(I, 28).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 11)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
End With
End With
End With
End With
End With
end sub
Connaissez vous une version qui pourrait s'executer plus rapidement sachant que mes feuilles 2007 - 2008 -
2009 - 2010 - 2011
contiennent environ 10000 lignes chacune?
merci d avance!
Bonjour
voici mon code
sub flk
Sheets("NON PAYEE TOTAL").Range("I3").Select
Selection.Copy
Range("J3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:úlse, Transpose:úlse
Sheets("NON PAYEE TOTAL").Range("I3") = ""
For I = Range("c65536").End(xlUp).Row To 7 Step -1
Cells(I, 1).EntireRow.Clear ' le 2 correspond au numero de colonne
Next
ligne = 2
With Sheets("2007")
For I = 1 To .[a65000].End(xlUp).Row
If .Cells(I, "P") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 13).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 17).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 25).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
With Sheets("2008")
For I = 1 To .[a65000].End(xlUp).Row
If .Cells(I, "Q") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 21).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 26).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
.Cells(I, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 11)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
With Sheets("2009")
For I = 1 To .[a65000].End(xlUp).Row
'MsgBox (.Cells(I, "Q"))
If .Cells(I, "Q") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 21).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 26).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
.Cells(I, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 11)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
With Sheets("2010")
For I = 1 To .[a65000].End(xlUp).Row
If .Cells(I, "Q") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 21).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
.Cells(I, 28).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 11)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
With Sheets("2011")
For I = 1 To .[a65000].End(xlUp).Row
If .Cells(I, "Q") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 21).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
.Cells(I, 28).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 11)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
End With
End With
End With
End With
End With
end sub
Connaissez vous une version qui pourrait s'executer plus rapidement sachant que mes feuilles 2007 - 2008 -
2009 - 2010 - 2011
contiennent environ 10000 lignes chacune?
merci d avance!
Bonjour
voici mon code
sub flk
Sheets("NON PAYEE TOTAL").Range("I3").Select
Selection.Copy
Range("J3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:úlse, Transpose:úlse
Sheets("NON PAYEE TOTAL").Range("I3") = ""
For I = Range("c65536").End(xlUp).Row To 7 Step -1
Cells(I, 1).EntireRow.Clear ' le 2 correspond au numero de colonne
Next
ligne = 2
With Sheets("2007")
For I = 1 To .[a65000].End(xlUp).Row
If .Cells(I, "P") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 13).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 17).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 25).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
With Sheets("2008")
For I = 1 To .[a65000].End(xlUp).Row
If .Cells(I, "Q") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 21).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 26).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
.Cells(I, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 11)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
With Sheets("2009")
For I = 1 To .[a65000].End(xlUp).Row
'MsgBox (.Cells(I, "Q"))
If .Cells(I, "Q") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 21).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 26).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
.Cells(I, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 11)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
With Sheets("2010")
For I = 1 To .[a65000].End(xlUp).Row
If .Cells(I, "Q") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 21).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
.Cells(I, 28).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 11)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
With Sheets("2011")
For I = 1 To .[a65000].End(xlUp).Row
If .Cells(I, "Q") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 21).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
.Cells(I, 28).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 11)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
End With
End With
End With
End With
End With
end sub
Connaissez vous une version qui pourrait s'executer plus rapidement sachant que mes feuilles 2007 - 2008 -
2009 - 2010 - 2011
contiennent environ 10000 lignes chacune?
merci d avance!
Bonjour,
Je n'ai pas compris; "ligne = 2" pour "Cells(ligne + 5, 1)"
et ceci
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
Si tous les blocs de copie sont identiques tu peux tester ceci
'------------------
Sub flk()
Dim i As Integer, sh As Worksheet
CalculActuel = Application.Calculation
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.ScreenUpdating = False
Set sh = Sheets("NON PAYEE TOTAL")
With sh
.[i3].Cut .[j3]
.Rows(Cells(.Rows.Count, 3).End(3).Row & ":7").Clear
End With
Ligne = 7
For i = 2007 To 2011
With Sheets(Trim(i))
With .Columns("P")
.AutoFilter Field:=1, Criteria1:="NON"
For Each c In
.Range("_FilterDataBase").SpecialCells(xlCellTypeVisible)
.Cells(c.Row, 1).Copy sh.Cells(Ligne, 1)
.Cells(c.Row, 3).Copy sh.Cells(Ligne, 2)
.Cells(c.Row, 13).Copy sh.Cells(Ligne, 3)
.Cells(c.Row, 14).Copy sh.Cells(Ligne, 4)
.Cells(c.Row, 15).Copy sh.Cells(Ligne, 5)
.Cells(c.Row, 17).Copy sh.Cells(Ligne, 6)
.Cells(c.Row, 18).Copy sh.Cells(Ligne, 7)
.Cells(c.Row, 19).Copy sh.Cells(Ligne, 8)
.Cells(c.Row, 20).Copy sh.Cells(Ligne, 9)
.Cells(c.Row, 25).Copy sh.Cells(Ligne, 10)
' .Cells(Ligne, 12) = "=YEAR(RC[-10])"
Ligne = Ligne + 1
Next
.AutoFilter
End With
End With
Next
Application.Calculation = CalculActuel
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
'---------------------------
--
Salutations
JJ
"STEPH B" a écrit dans le message de news:
4d469ca6$0$7723$Bonjour
voici mon code
sub flk
Sheets("NON PAYEE TOTAL").Range("I3").Select
Selection.Copy
Range("J3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:úlse, Transpose:úlse
Sheets("NON PAYEE TOTAL").Range("I3") = ""
For I = Range("c65536").End(xlUp).Row To 7 Step -1
Cells(I, 1).EntireRow.Clear ' le 2 correspond au numero de colonne
Next
ligne = 2
With Sheets("2007")
For I = 1 To .[a65000].End(xlUp).Row
If .Cells(I, "P") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 13).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 17).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 25).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
With Sheets("2008")
For I = 1 To .[a65000].End(xlUp).Row
If .Cells(I, "Q") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 21).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 26).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
.Cells(I, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 11)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
With Sheets("2009")
For I = 1 To .[a65000].End(xlUp).Row
'MsgBox (.Cells(I, "Q"))
If .Cells(I, "Q") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 21).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 26).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
.Cells(I, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 11)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
With Sheets("2010")
For I = 1 To .[a65000].End(xlUp).Row
If .Cells(I, "Q") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 21).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
.Cells(I, 28).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 11)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
With Sheets("2011")
For I = 1 To .[a65000].End(xlUp).Row
If .Cells(I, "Q") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 21).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
.Cells(I, 28).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 11)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
End With
End With
End With
End With
End With
end sub
Connaissez vous une version qui pourrait s'executer plus rapidement
sachant que mes feuilles 2007 - 2008 - 2009 - 2010 - 2011
contiennent environ 10000 lignes chacune?
merci d avance!
Bonjour,
Je n'ai pas compris; "ligne = 2" pour "Cells(ligne + 5, 1)"
et ceci
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
Si tous les blocs de copie sont identiques tu peux tester ceci
'------------------
Sub flk()
Dim i As Integer, sh As Worksheet
CalculActuel = Application.Calculation
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.ScreenUpdating = False
Set sh = Sheets("NON PAYEE TOTAL")
With sh
.[i3].Cut .[j3]
.Rows(Cells(.Rows.Count, 3).End(3).Row & ":7").Clear
End With
Ligne = 7
For i = 2007 To 2011
With Sheets(Trim(i))
With .Columns("P")
.AutoFilter Field:=1, Criteria1:="NON"
For Each c In
.Range("_FilterDataBase").SpecialCells(xlCellTypeVisible)
.Cells(c.Row, 1).Copy sh.Cells(Ligne, 1)
.Cells(c.Row, 3).Copy sh.Cells(Ligne, 2)
.Cells(c.Row, 13).Copy sh.Cells(Ligne, 3)
.Cells(c.Row, 14).Copy sh.Cells(Ligne, 4)
.Cells(c.Row, 15).Copy sh.Cells(Ligne, 5)
.Cells(c.Row, 17).Copy sh.Cells(Ligne, 6)
.Cells(c.Row, 18).Copy sh.Cells(Ligne, 7)
.Cells(c.Row, 19).Copy sh.Cells(Ligne, 8)
.Cells(c.Row, 20).Copy sh.Cells(Ligne, 9)
.Cells(c.Row, 25).Copy sh.Cells(Ligne, 10)
' .Cells(Ligne, 12) = "=YEAR(RC[-10])"
Ligne = Ligne + 1
Next
.AutoFilter
End With
End With
Next
Application.Calculation = CalculActuel
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
'---------------------------
--
Salutations
JJ
"STEPH B" <stephol@prodiag.fr> a écrit dans le message de news:
4d469ca6$0$7723$ba4acef3@reader.news.orange.fr...
Bonjour
voici mon code
sub flk
Sheets("NON PAYEE TOTAL").Range("I3").Select
Selection.Copy
Range("J3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:úlse, Transpose:úlse
Sheets("NON PAYEE TOTAL").Range("I3") = ""
For I = Range("c65536").End(xlUp).Row To 7 Step -1
Cells(I, 1).EntireRow.Clear ' le 2 correspond au numero de colonne
Next
ligne = 2
With Sheets("2007")
For I = 1 To .[a65000].End(xlUp).Row
If .Cells(I, "P") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 13).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 17).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 25).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
With Sheets("2008")
For I = 1 To .[a65000].End(xlUp).Row
If .Cells(I, "Q") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 21).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 26).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
.Cells(I, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 11)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
With Sheets("2009")
For I = 1 To .[a65000].End(xlUp).Row
'MsgBox (.Cells(I, "Q"))
If .Cells(I, "Q") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 21).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 26).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
.Cells(I, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 11)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
With Sheets("2010")
For I = 1 To .[a65000].End(xlUp).Row
If .Cells(I, "Q") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 21).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
.Cells(I, 28).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 11)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
With Sheets("2011")
For I = 1 To .[a65000].End(xlUp).Row
If .Cells(I, "Q") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 21).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
.Cells(I, 28).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 11)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
End With
End With
End With
End With
End With
end sub
Connaissez vous une version qui pourrait s'executer plus rapidement
sachant que mes feuilles 2007 - 2008 - 2009 - 2010 - 2011
contiennent environ 10000 lignes chacune?
merci d avance!
Bonjour,
Je n'ai pas compris; "ligne = 2" pour "Cells(ligne + 5, 1)"
et ceci
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
Si tous les blocs de copie sont identiques tu peux tester ceci
'------------------
Sub flk()
Dim i As Integer, sh As Worksheet
CalculActuel = Application.Calculation
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.ScreenUpdating = False
Set sh = Sheets("NON PAYEE TOTAL")
With sh
.[i3].Cut .[j3]
.Rows(Cells(.Rows.Count, 3).End(3).Row & ":7").Clear
End With
Ligne = 7
For i = 2007 To 2011
With Sheets(Trim(i))
With .Columns("P")
.AutoFilter Field:=1, Criteria1:="NON"
For Each c In
.Range("_FilterDataBase").SpecialCells(xlCellTypeVisible)
.Cells(c.Row, 1).Copy sh.Cells(Ligne, 1)
.Cells(c.Row, 3).Copy sh.Cells(Ligne, 2)
.Cells(c.Row, 13).Copy sh.Cells(Ligne, 3)
.Cells(c.Row, 14).Copy sh.Cells(Ligne, 4)
.Cells(c.Row, 15).Copy sh.Cells(Ligne, 5)
.Cells(c.Row, 17).Copy sh.Cells(Ligne, 6)
.Cells(c.Row, 18).Copy sh.Cells(Ligne, 7)
.Cells(c.Row, 19).Copy sh.Cells(Ligne, 8)
.Cells(c.Row, 20).Copy sh.Cells(Ligne, 9)
.Cells(c.Row, 25).Copy sh.Cells(Ligne, 10)
' .Cells(Ligne, 12) = "=YEAR(RC[-10])"
Ligne = Ligne + 1
Next
.AutoFilter
End With
End With
Next
Application.Calculation = CalculActuel
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
'---------------------------
--
Salutations
JJ
"STEPH B" a écrit dans le message de news:
4d469ca6$0$7723$Bonjour
voici mon code
sub flk
Sheets("NON PAYEE TOTAL").Range("I3").Select
Selection.Copy
Range("J3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:úlse, Transpose:úlse
Sheets("NON PAYEE TOTAL").Range("I3") = ""
For I = Range("c65536").End(xlUp).Row To 7 Step -1
Cells(I, 1).EntireRow.Clear ' le 2 correspond au numero de colonne
Next
ligne = 2
With Sheets("2007")
For I = 1 To .[a65000].End(xlUp).Row
If .Cells(I, "P") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 13).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 17).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 25).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
With Sheets("2008")
For I = 1 To .[a65000].End(xlUp).Row
If .Cells(I, "Q") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 21).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 26).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
.Cells(I, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 11)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
With Sheets("2009")
For I = 1 To .[a65000].End(xlUp).Row
'MsgBox (.Cells(I, "Q"))
If .Cells(I, "Q") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 21).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 26).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
.Cells(I, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 11)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
With Sheets("2010")
For I = 1 To .[a65000].End(xlUp).Row
If .Cells(I, "Q") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 21).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
.Cells(I, 28).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 11)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
With Sheets("2011")
For I = 1 To .[a65000].End(xlUp).Row
If .Cells(I, "Q") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 21).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
.Cells(I, 28).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 11)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
End With
End With
End With
End With
End With
end sub
Connaissez vous une version qui pourrait s'executer plus rapidement
sachant que mes feuilles 2007 - 2008 - 2009 - 2010 - 2011
contiennent environ 10000 lignes chacune?
merci d avance!
justement non tous les blocks ne sont pas identiques....
"Jacky" a écrit dans le message de news: ii6ke1$6rd$Bonjour,
Je n'ai pas compris; "ligne = 2" pour "Cells(ligne + 5, 1)"
et ceci
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
Si tous les blocs de copie sont identiques tu peux tester ceci
'------------------
Sub flk()
Dim i As Integer, sh As Worksheet
CalculActuel = Application.Calculation
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.ScreenUpdating = False
Set sh = Sheets("NON PAYEE TOTAL")
With sh
.[i3].Cut .[j3]
.Rows(Cells(.Rows.Count, 3).End(3).Row & ":7").Clear
End With
Ligne = 7
For i = 2007 To 2011
With Sheets(Trim(i))
With .Columns("P")
.AutoFilter Field:=1, Criteria1:="NON"
For Each c In .Range("_FilterDataBase").SpecialCells(xlCellTypeVisible)
.Cells(c.Row, 1).Copy sh.Cells(Ligne, 1)
.Cells(c.Row, 3).Copy sh.Cells(Ligne, 2)
.Cells(c.Row, 13).Copy sh.Cells(Ligne, 3)
.Cells(c.Row, 14).Copy sh.Cells(Ligne, 4)
.Cells(c.Row, 15).Copy sh.Cells(Ligne, 5)
.Cells(c.Row, 17).Copy sh.Cells(Ligne, 6)
.Cells(c.Row, 18).Copy sh.Cells(Ligne, 7)
.Cells(c.Row, 19).Copy sh.Cells(Ligne, 8)
.Cells(c.Row, 20).Copy sh.Cells(Ligne, 9)
.Cells(c.Row, 25).Copy sh.Cells(Ligne, 10)
' .Cells(Ligne, 12) = "=YEAR(RC[-10])"
Ligne = Ligne + 1
Next
.AutoFilter
End With
End With
Next
Application.Calculation = CalculActuel
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
'---------------------------
--
Salutations
JJ
"STEPH B" a écrit dans le message de news:
4d469ca6$0$7723$Bonjour
voici mon code
sub flk
Sheets("NON PAYEE TOTAL").Range("I3").Select
Selection.Copy
Range("J3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:úlse, Transpose:úlse
Sheets("NON PAYEE TOTAL").Range("I3") = ""
For I = Range("c65536").End(xlUp).Row To 7 Step -1
Cells(I, 1).EntireRow.Clear ' le 2 correspond au numero de colonne
Next
ligne = 2
With Sheets("2007")
For I = 1 To .[a65000].End(xlUp).Row
If .Cells(I, "P") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 13).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 17).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 25).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
With Sheets("2008")
For I = 1 To .[a65000].End(xlUp).Row
If .Cells(I, "Q") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 21).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 26).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
.Cells(I, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 11)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
With Sheets("2009")
For I = 1 To .[a65000].End(xlUp).Row
'MsgBox (.Cells(I, "Q"))
If .Cells(I, "Q") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 21).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 26).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
.Cells(I, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 11)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
With Sheets("2010")
For I = 1 To .[a65000].End(xlUp).Row
If .Cells(I, "Q") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 21).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
.Cells(I, 28).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 11)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
With Sheets("2011")
For I = 1 To .[a65000].End(xlUp).Row
If .Cells(I, "Q") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 21).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
.Cells(I, 28).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 11)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
End With
End With
End With
End With
End With
end sub
Connaissez vous une version qui pourrait s'executer plus rapidement sachant que mes feuilles 2007 - 2008 -
2009 - 2010 - 2011
contiennent environ 10000 lignes chacune?
merci d avance!
justement non tous les blocks ne sont pas identiques....
"Jacky" <Dupond@marcel.fr> a écrit dans le message de news: ii6ke1$6rd$1@speranza.aioe.org...
Bonjour,
Je n'ai pas compris; "ligne = 2" pour "Cells(ligne + 5, 1)"
et ceci
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
Si tous les blocs de copie sont identiques tu peux tester ceci
'------------------
Sub flk()
Dim i As Integer, sh As Worksheet
CalculActuel = Application.Calculation
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.ScreenUpdating = False
Set sh = Sheets("NON PAYEE TOTAL")
With sh
.[i3].Cut .[j3]
.Rows(Cells(.Rows.Count, 3).End(3).Row & ":7").Clear
End With
Ligne = 7
For i = 2007 To 2011
With Sheets(Trim(i))
With .Columns("P")
.AutoFilter Field:=1, Criteria1:="NON"
For Each c In .Range("_FilterDataBase").SpecialCells(xlCellTypeVisible)
.Cells(c.Row, 1).Copy sh.Cells(Ligne, 1)
.Cells(c.Row, 3).Copy sh.Cells(Ligne, 2)
.Cells(c.Row, 13).Copy sh.Cells(Ligne, 3)
.Cells(c.Row, 14).Copy sh.Cells(Ligne, 4)
.Cells(c.Row, 15).Copy sh.Cells(Ligne, 5)
.Cells(c.Row, 17).Copy sh.Cells(Ligne, 6)
.Cells(c.Row, 18).Copy sh.Cells(Ligne, 7)
.Cells(c.Row, 19).Copy sh.Cells(Ligne, 8)
.Cells(c.Row, 20).Copy sh.Cells(Ligne, 9)
.Cells(c.Row, 25).Copy sh.Cells(Ligne, 10)
' .Cells(Ligne, 12) = "=YEAR(RC[-10])"
Ligne = Ligne + 1
Next
.AutoFilter
End With
End With
Next
Application.Calculation = CalculActuel
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
'---------------------------
--
Salutations
JJ
"STEPH B" <stephol@prodiag.fr> a écrit dans le message de news:
4d469ca6$0$7723$ba4acef3@reader.news.orange.fr...
Bonjour
voici mon code
sub flk
Sheets("NON PAYEE TOTAL").Range("I3").Select
Selection.Copy
Range("J3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:úlse, Transpose:úlse
Sheets("NON PAYEE TOTAL").Range("I3") = ""
For I = Range("c65536").End(xlUp).Row To 7 Step -1
Cells(I, 1).EntireRow.Clear ' le 2 correspond au numero de colonne
Next
ligne = 2
With Sheets("2007")
For I = 1 To .[a65000].End(xlUp).Row
If .Cells(I, "P") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 13).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 17).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 25).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
With Sheets("2008")
For I = 1 To .[a65000].End(xlUp).Row
If .Cells(I, "Q") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 21).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 26).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
.Cells(I, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 11)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
With Sheets("2009")
For I = 1 To .[a65000].End(xlUp).Row
'MsgBox (.Cells(I, "Q"))
If .Cells(I, "Q") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 21).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 26).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
.Cells(I, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 11)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
With Sheets("2010")
For I = 1 To .[a65000].End(xlUp).Row
If .Cells(I, "Q") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 21).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
.Cells(I, 28).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 11)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
With Sheets("2011")
For I = 1 To .[a65000].End(xlUp).Row
If .Cells(I, "Q") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 21).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
.Cells(I, 28).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 11)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
End With
End With
End With
End With
End With
end sub
Connaissez vous une version qui pourrait s'executer plus rapidement sachant que mes feuilles 2007 - 2008 -
2009 - 2010 - 2011
contiennent environ 10000 lignes chacune?
merci d avance!
justement non tous les blocks ne sont pas identiques....
"Jacky" a écrit dans le message de news: ii6ke1$6rd$Bonjour,
Je n'ai pas compris; "ligne = 2" pour "Cells(ligne + 5, 1)"
et ceci
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
Si tous les blocs de copie sont identiques tu peux tester ceci
'------------------
Sub flk()
Dim i As Integer, sh As Worksheet
CalculActuel = Application.Calculation
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.ScreenUpdating = False
Set sh = Sheets("NON PAYEE TOTAL")
With sh
.[i3].Cut .[j3]
.Rows(Cells(.Rows.Count, 3).End(3).Row & ":7").Clear
End With
Ligne = 7
For i = 2007 To 2011
With Sheets(Trim(i))
With .Columns("P")
.AutoFilter Field:=1, Criteria1:="NON"
For Each c In .Range("_FilterDataBase").SpecialCells(xlCellTypeVisible)
.Cells(c.Row, 1).Copy sh.Cells(Ligne, 1)
.Cells(c.Row, 3).Copy sh.Cells(Ligne, 2)
.Cells(c.Row, 13).Copy sh.Cells(Ligne, 3)
.Cells(c.Row, 14).Copy sh.Cells(Ligne, 4)
.Cells(c.Row, 15).Copy sh.Cells(Ligne, 5)
.Cells(c.Row, 17).Copy sh.Cells(Ligne, 6)
.Cells(c.Row, 18).Copy sh.Cells(Ligne, 7)
.Cells(c.Row, 19).Copy sh.Cells(Ligne, 8)
.Cells(c.Row, 20).Copy sh.Cells(Ligne, 9)
.Cells(c.Row, 25).Copy sh.Cells(Ligne, 10)
' .Cells(Ligne, 12) = "=YEAR(RC[-10])"
Ligne = Ligne + 1
Next
.AutoFilter
End With
End With
Next
Application.Calculation = CalculActuel
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
'---------------------------
--
Salutations
JJ
"STEPH B" a écrit dans le message de news:
4d469ca6$0$7723$Bonjour
voici mon code
sub flk
Sheets("NON PAYEE TOTAL").Range("I3").Select
Selection.Copy
Range("J3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:úlse, Transpose:úlse
Sheets("NON PAYEE TOTAL").Range("I3") = ""
For I = Range("c65536").End(xlUp).Row To 7 Step -1
Cells(I, 1).EntireRow.Clear ' le 2 correspond au numero de colonne
Next
ligne = 2
With Sheets("2007")
For I = 1 To .[a65000].End(xlUp).Row
If .Cells(I, "P") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 13).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 17).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 25).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
With Sheets("2008")
For I = 1 To .[a65000].End(xlUp).Row
If .Cells(I, "Q") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 21).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 26).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
.Cells(I, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 11)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
With Sheets("2009")
For I = 1 To .[a65000].End(xlUp).Row
'MsgBox (.Cells(I, "Q"))
If .Cells(I, "Q") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 21).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 26).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
.Cells(I, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 11)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
With Sheets("2010")
For I = 1 To .[a65000].End(xlUp).Row
If .Cells(I, "Q") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 21).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
.Cells(I, 28).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 11)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
With Sheets("2011")
For I = 1 To .[a65000].End(xlUp).Row
If .Cells(I, "Q") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 21).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
.Cells(I, 28).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 11)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
End With
End With
End With
End With
End With
end sub
Connaissez vous une version qui pourrait s'executer plus rapidement sachant que mes feuilles 2007 - 2008 -
2009 - 2010 - 2011
contiennent environ 10000 lignes chacune?
merci d avance!
Bonjour
voici mon code
sub flk
Sheets("NON PAYEE TOTAL").Range("I3").Select
Selection.Copy
Range("J3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:úlse, Transpose:úlse
Sheets("NON PAYEE TOTAL").Range("I3") = ""
For I = Range("c65536").End(xlUp).Row To 7 Step -1
Cells(I, 1).EntireRow.Clear ' le 2 correspond au numero de colonne
Next
ligne = 2
With Sheets("2007")
For I = 1 To .[a65000].End(xlUp).Row
If .Cells(I, "P") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 13).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 17).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 25).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
With Sheets("2008")
For I = 1 To .[a65000].End(xlUp).Row
If .Cells(I, "Q") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 21).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 26).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
.Cells(I, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 11)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
With Sheets("2009")
For I = 1 To .[a65000].End(xlUp).Row
'MsgBox (.Cells(I, "Q"))
If .Cells(I, "Q") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 21).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 26).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
.Cells(I, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 11)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
With Sheets("2010")
For I = 1 To .[a65000].End(xlUp).Row
If .Cells(I, "Q") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 21).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
.Cells(I, 28).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 11)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
With Sheets("2011")
For I = 1 To .[a65000].End(xlUp).Row
If .Cells(I, "Q") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 21).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
.Cells(I, 28).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 11)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
End With
End With
End With
End With
End With
end sub
Connaissez vous une version qui pourrait s'executer plus rapidement sachant
que mes feuilles 2007 - 2008 - 2009 - 2010 - 2011
contiennent environ 10000 lignes chacune?
merci d avance!
Bonjour
voici mon code
sub flk
Sheets("NON PAYEE TOTAL").Range("I3").Select
Selection.Copy
Range("J3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:úlse, Transpose:úlse
Sheets("NON PAYEE TOTAL").Range("I3") = ""
For I = Range("c65536").End(xlUp).Row To 7 Step -1
Cells(I, 1).EntireRow.Clear ' le 2 correspond au numero de colonne
Next
ligne = 2
With Sheets("2007")
For I = 1 To .[a65000].End(xlUp).Row
If .Cells(I, "P") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 13).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 17).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 25).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
With Sheets("2008")
For I = 1 To .[a65000].End(xlUp).Row
If .Cells(I, "Q") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 21).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 26).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
.Cells(I, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 11)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
With Sheets("2009")
For I = 1 To .[a65000].End(xlUp).Row
'MsgBox (.Cells(I, "Q"))
If .Cells(I, "Q") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 21).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 26).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
.Cells(I, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 11)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
With Sheets("2010")
For I = 1 To .[a65000].End(xlUp).Row
If .Cells(I, "Q") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 21).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
.Cells(I, 28).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 11)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
With Sheets("2011")
For I = 1 To .[a65000].End(xlUp).Row
If .Cells(I, "Q") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 21).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
.Cells(I, 28).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 11)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
End With
End With
End With
End With
End With
end sub
Connaissez vous une version qui pourrait s'executer plus rapidement sachant
que mes feuilles 2007 - 2008 - 2009 - 2010 - 2011
contiennent environ 10000 lignes chacune?
merci d avance!
Bonjour
voici mon code
sub flk
Sheets("NON PAYEE TOTAL").Range("I3").Select
Selection.Copy
Range("J3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:úlse, Transpose:úlse
Sheets("NON PAYEE TOTAL").Range("I3") = ""
For I = Range("c65536").End(xlUp).Row To 7 Step -1
Cells(I, 1).EntireRow.Clear ' le 2 correspond au numero de colonne
Next
ligne = 2
With Sheets("2007")
For I = 1 To .[a65000].End(xlUp).Row
If .Cells(I, "P") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 13).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 17).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 25).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
With Sheets("2008")
For I = 1 To .[a65000].End(xlUp).Row
If .Cells(I, "Q") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 21).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 26).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
.Cells(I, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 11)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
With Sheets("2009")
For I = 1 To .[a65000].End(xlUp).Row
'MsgBox (.Cells(I, "Q"))
If .Cells(I, "Q") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 21).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 26).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
.Cells(I, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 11)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
With Sheets("2010")
For I = 1 To .[a65000].End(xlUp).Row
If .Cells(I, "Q") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 21).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
.Cells(I, 28).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 11)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
With Sheets("2011")
For I = 1 To .[a65000].End(xlUp).Row
If .Cells(I, "Q") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 21).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
.Cells(I, 28).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 11)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
End With
End With
End With
End With
End With
end sub
Connaissez vous une version qui pourrait s'executer plus rapidement sachant
que mes feuilles 2007 - 2008 - 2009 - 2010 - 2011
contiennent environ 10000 lignes chacune?
merci d avance!
Dans son message précédent, STEPH B a écrit :Bonjour
voici mon code
sub flk
Sheets("NON PAYEE TOTAL").Range("I3").Select
Selection.Copy
Range("J3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:úlse, Transpose:úlse
Sheets("NON PAYEE TOTAL").Range("I3") = ""
For I = Range("c65536").End(xlUp).Row To 7 Step -1
Cells(I, 1).EntireRow.Clear ' le 2 correspond au numero de colonne
Next
ligne = 2
With Sheets("2007")
For I = 1 To .[a65000].End(xlUp).Row
If .Cells(I, "P") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 13).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 17).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 25).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
With Sheets("2008")
For I = 1 To .[a65000].End(xlUp).Row
If .Cells(I, "Q") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 21).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 26).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
.Cells(I, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 11)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
With Sheets("2009")
For I = 1 To .[a65000].End(xlUp).Row
'MsgBox (.Cells(I, "Q"))
If .Cells(I, "Q") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 21).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 26).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
.Cells(I, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 11)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
With Sheets("2010")
For I = 1 To .[a65000].End(xlUp).Row
If .Cells(I, "Q") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 21).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
.Cells(I, 28).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 11)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
With Sheets("2011")
For I = 1 To .[a65000].End(xlUp).Row
If .Cells(I, "Q") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 21).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
.Cells(I, 28).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 11)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
End With
End With
End With
End With
End With
end sub
Connaissez vous une version qui pourrait s'executer plus rapidement
sachant que mes feuilles 2007 - 2008 - 2009 - 2010 - 2011
contiennent environ 10000 lignes chacune?
merci d avance!
Bonsoir,
Une piste peut-être ?
Voir le fichier:
http://www.cijoint.fr/cjlink.php?file=cj201101/cijfxaw3IM.zip
Le traitement de cinq années comportant 10000 lignes chacune prend environ
17s (sur ma bécane)
Bon courage.
Dans son message précédent, STEPH B a écrit :
Bonjour
voici mon code
sub flk
Sheets("NON PAYEE TOTAL").Range("I3").Select
Selection.Copy
Range("J3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:úlse, Transpose:úlse
Sheets("NON PAYEE TOTAL").Range("I3") = ""
For I = Range("c65536").End(xlUp).Row To 7 Step -1
Cells(I, 1).EntireRow.Clear ' le 2 correspond au numero de colonne
Next
ligne = 2
With Sheets("2007")
For I = 1 To .[a65000].End(xlUp).Row
If .Cells(I, "P") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 13).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 17).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 25).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
With Sheets("2008")
For I = 1 To .[a65000].End(xlUp).Row
If .Cells(I, "Q") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 21).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 26).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
.Cells(I, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 11)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
With Sheets("2009")
For I = 1 To .[a65000].End(xlUp).Row
'MsgBox (.Cells(I, "Q"))
If .Cells(I, "Q") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 21).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 26).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
.Cells(I, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 11)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
With Sheets("2010")
For I = 1 To .[a65000].End(xlUp).Row
If .Cells(I, "Q") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 21).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
.Cells(I, 28).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 11)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
With Sheets("2011")
For I = 1 To .[a65000].End(xlUp).Row
If .Cells(I, "Q") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 21).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
.Cells(I, 28).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 11)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
End With
End With
End With
End With
End With
end sub
Connaissez vous une version qui pourrait s'executer plus rapidement
sachant que mes feuilles 2007 - 2008 - 2009 - 2010 - 2011
contiennent environ 10000 lignes chacune?
merci d avance!
Bonsoir,
Une piste peut-être ?
Voir le fichier:
http://www.cijoint.fr/cjlink.php?file=cj201101/cijfxaw3IM.zip
Le traitement de cinq années comportant 10000 lignes chacune prend environ
17s (sur ma bécane)
Bon courage.
Dans son message précédent, STEPH B a écrit :Bonjour
voici mon code
sub flk
Sheets("NON PAYEE TOTAL").Range("I3").Select
Selection.Copy
Range("J3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:úlse, Transpose:úlse
Sheets("NON PAYEE TOTAL").Range("I3") = ""
For I = Range("c65536").End(xlUp).Row To 7 Step -1
Cells(I, 1).EntireRow.Clear ' le 2 correspond au numero de colonne
Next
ligne = 2
With Sheets("2007")
For I = 1 To .[a65000].End(xlUp).Row
If .Cells(I, "P") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 13).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 17).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 25).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
With Sheets("2008")
For I = 1 To .[a65000].End(xlUp).Row
If .Cells(I, "Q") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 21).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 26).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
.Cells(I, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 11)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
With Sheets("2009")
For I = 1 To .[a65000].End(xlUp).Row
'MsgBox (.Cells(I, "Q"))
If .Cells(I, "Q") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 21).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 26).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
.Cells(I, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 11)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
With Sheets("2010")
For I = 1 To .[a65000].End(xlUp).Row
If .Cells(I, "Q") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 21).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
.Cells(I, 28).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 11)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
With Sheets("2011")
For I = 1 To .[a65000].End(xlUp).Row
If .Cells(I, "Q") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 21).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
.Cells(I, 28).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 11)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
End With
End With
End With
End With
End With
end sub
Connaissez vous une version qui pourrait s'executer plus rapidement
sachant que mes feuilles 2007 - 2008 - 2009 - 2010 - 2011
contiennent environ 10000 lignes chacune?
merci d avance!
Bonsoir,
Une piste peut-être ?
Voir le fichier:
http://www.cijoint.fr/cjlink.php?file=cj201101/cijfxaw3IM.zip
Le traitement de cinq années comportant 10000 lignes chacune prend environ
17s (sur ma bécane)
Bon courage.
Dans son message précédent, STEPH B a écrit :Bonjour
voici mon code
sub flk
Sheets("NON PAYEE TOTAL").Range("I3").Select
Selection.Copy
Range("J3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:úlse, Transpose:úlse
Sheets("NON PAYEE TOTAL").Range("I3") = ""
For I = Range("c65536").End(xlUp).Row To 7 Step -1
Cells(I, 1).EntireRow.Clear ' le 2 correspond au numero de colonne
Next
ligne = 2
With Sheets("2007")
For I = 1 To .[a65000].End(xlUp).Row
If .Cells(I, "P") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 13).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 17).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 25).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
With Sheets("2008")
For I = 1 To .[a65000].End(xlUp).Row
If .Cells(I, "Q") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 21).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 26).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
.Cells(I, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 11)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
With Sheets("2009")
For I = 1 To .[a65000].End(xlUp).Row
'MsgBox (.Cells(I, "Q"))
If .Cells(I, "Q") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 21).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 26).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
.Cells(I, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 11)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
With Sheets("2010")
For I = 1 To .[a65000].End(xlUp).Row
If .Cells(I, "Q") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 21).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
.Cells(I, 28).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 11)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
With Sheets("2011")
For I = 1 To .[a65000].End(xlUp).Row
If .Cells(I, "Q") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 21).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
.Cells(I, 28).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 11)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
End With
End With
End With
End With
End With
end sub
Connaissez vous une version qui pourrait s'executer plus rapidement
sachant que mes feuilles 2007 - 2008 - 2009 - 2010 - 2011
contiennent environ 10000 lignes chacune?
merci d avance!
Bonsoir,
Une piste peut-être ?
Voir le fichier:
http://www.cijoint.fr/cjlink.php?file=cj201101/cijfxaw3IM.zip
Le traitement de cinq années comportant 10000 lignes chacune prend environ
17s (sur ma bécane)
Bon courage.
Dans son message précédent, STEPH B a écrit :
Bonjour
voici mon code
sub flk
Sheets("NON PAYEE TOTAL").Range("I3").Select
Selection.Copy
Range("J3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:úlse, Transpose:úlse
Sheets("NON PAYEE TOTAL").Range("I3") = ""
For I = Range("c65536").End(xlUp).Row To 7 Step -1
Cells(I, 1).EntireRow.Clear ' le 2 correspond au numero de colonne
Next
ligne = 2
With Sheets("2007")
For I = 1 To .[a65000].End(xlUp).Row
If .Cells(I, "P") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 13).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 17).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 25).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
With Sheets("2008")
For I = 1 To .[a65000].End(xlUp).Row
If .Cells(I, "Q") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 21).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 26).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
.Cells(I, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 11)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
With Sheets("2009")
For I = 1 To .[a65000].End(xlUp).Row
'MsgBox (.Cells(I, "Q"))
If .Cells(I, "Q") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 21).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 26).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
.Cells(I, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 11)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
With Sheets("2010")
For I = 1 To .[a65000].End(xlUp).Row
If .Cells(I, "Q") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 21).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
.Cells(I, 28).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 11)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
With Sheets("2011")
For I = 1 To .[a65000].End(xlUp).Row
If .Cells(I, "Q") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 21).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
.Cells(I, 28).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 11)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
End With
End With
End With
End With
End With
end sub
Connaissez vous une version qui pourrait s'executer plus rapidement
sachant que mes feuilles 2007 - 2008 - 2009 - 2010 - 2011
contiennent environ 10000 lignes chacune?
merci d avance!
Bonsoir,
Une piste peut-être ?
Voir le fichier:
http://www.cijoint.fr/cjlink.php?file=cj201101/cijfxaw3IM.zip
Le traitement de cinq années comportant 10000 lignes chacune prend environ
17s (sur ma bécane)
Bon courage.
Dans son message précédent, STEPH B a écrit :Bonjour
voici mon code
sub flk
Sheets("NON PAYEE TOTAL").Range("I3").Select
Selection.Copy
Range("J3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:úlse, Transpose:úlse
Sheets("NON PAYEE TOTAL").Range("I3") = ""
For I = Range("c65536").End(xlUp).Row To 7 Step -1
Cells(I, 1).EntireRow.Clear ' le 2 correspond au numero de colonne
Next
ligne = 2
With Sheets("2007")
For I = 1 To .[a65000].End(xlUp).Row
If .Cells(I, "P") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 13).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 17).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 25).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
With Sheets("2008")
For I = 1 To .[a65000].End(xlUp).Row
If .Cells(I, "Q") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 21).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 26).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
.Cells(I, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 11)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
With Sheets("2009")
For I = 1 To .[a65000].End(xlUp).Row
'MsgBox (.Cells(I, "Q"))
If .Cells(I, "Q") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 21).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 26).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
.Cells(I, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 11)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
With Sheets("2010")
For I = 1 To .[a65000].End(xlUp).Row
If .Cells(I, "Q") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 21).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
.Cells(I, 28).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 11)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
With Sheets("2011")
For I = 1 To .[a65000].End(xlUp).Row
If .Cells(I, "Q") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 21).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
.Cells(I, 28).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 11)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
End With
End With
End With
End With
End With
end sub
Connaissez vous une version qui pourrait s'executer plus rapidement
sachant que mes feuilles 2007 - 2008 - 2009 - 2010 - 2011
contiennent environ 10000 lignes chacune?
merci d avance!
Bonsoir,
Une piste peut-être ?
Voir le fichier:
http://www.cijoint.fr/cjlink.php?file=cj201101/cijfxaw3IM.zip
Le traitement de cinq années comportant 10000 lignes chacune prend environ
17s (sur ma bécane)
Bon courage.
charabeuh ca marche
en fait
juste un beug sur les années 2007 car les "NON" sont en colonne P et non Q
"Charabeuh" a écrit dans le message de news:
ii75un$7on$Dans son message précédent, STEPH B a écrit :Bonjour
voici mon code
sub flk
Sheets("NON PAYEE TOTAL").Range("I3").Select
Selection.Copy
Range("J3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:úlse, Transpose:úlse
Sheets("NON PAYEE TOTAL").Range("I3") = ""
For I = Range("c65536").End(xlUp).Row To 7 Step -1
Cells(I, 1).EntireRow.Clear ' le 2 correspond au numero de colonne
Next
ligne = 2
With Sheets("2007")
For I = 1 To .[a65000].End(xlUp).Row
If .Cells(I, "P") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 13).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 17).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 25).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
With Sheets("2008")
For I = 1 To .[a65000].End(xlUp).Row
If .Cells(I, "Q") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 21).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 26).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
.Cells(I, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 11)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
With Sheets("2009")
For I = 1 To .[a65000].End(xlUp).Row
'MsgBox (.Cells(I, "Q"))
If .Cells(I, "Q") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 21).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 26).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
.Cells(I, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5,
11)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
With Sheets("2010")
For I = 1 To .[a65000].End(xlUp).Row
If .Cells(I, "Q") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 21).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
.Cells(I, 28).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5,
11)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
With Sheets("2011")
For I = 1 To .[a65000].End(xlUp).Row
If .Cells(I, "Q") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 21).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
.Cells(I, 28).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5,
11)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
End With
End With
End With
End With
End With
end sub
Connaissez vous une version qui pourrait s'executer plus rapidement
sachant que mes feuilles 2007 - 2008 - 2009 - 2010 - 2011
contiennent environ 10000 lignes chacune?
merci d avance!
Bonsoir,
Une piste peut-être ?
Voir le fichier:
http://www.cijoint.fr/cjlink.php?file=cj201101/cijfxaw3IM.zip
Le traitement de cinq années comportant 10000 lignes chacune prend
environ 17s (sur ma bécane)
Bon courage.
charabeuh ca marche
en fait
juste un beug sur les années 2007 car les "NON" sont en colonne P et non Q
"Charabeuh" <please@feed.back> a écrit dans le message de news:
ii75un$7on$1@speranza.aioe.org...
Dans son message précédent, STEPH B a écrit :
Bonjour
voici mon code
sub flk
Sheets("NON PAYEE TOTAL").Range("I3").Select
Selection.Copy
Range("J3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:úlse, Transpose:úlse
Sheets("NON PAYEE TOTAL").Range("I3") = ""
For I = Range("c65536").End(xlUp).Row To 7 Step -1
Cells(I, 1).EntireRow.Clear ' le 2 correspond au numero de colonne
Next
ligne = 2
With Sheets("2007")
For I = 1 To .[a65000].End(xlUp).Row
If .Cells(I, "P") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 13).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 17).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 25).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
With Sheets("2008")
For I = 1 To .[a65000].End(xlUp).Row
If .Cells(I, "Q") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 21).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 26).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
.Cells(I, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 11)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
With Sheets("2009")
For I = 1 To .[a65000].End(xlUp).Row
'MsgBox (.Cells(I, "Q"))
If .Cells(I, "Q") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 21).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 26).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
.Cells(I, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5,
11)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
With Sheets("2010")
For I = 1 To .[a65000].End(xlUp).Row
If .Cells(I, "Q") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 21).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
.Cells(I, 28).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5,
11)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
With Sheets("2011")
For I = 1 To .[a65000].End(xlUp).Row
If .Cells(I, "Q") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 21).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
.Cells(I, 28).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5,
11)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
End With
End With
End With
End With
End With
end sub
Connaissez vous une version qui pourrait s'executer plus rapidement
sachant que mes feuilles 2007 - 2008 - 2009 - 2010 - 2011
contiennent environ 10000 lignes chacune?
merci d avance!
Bonsoir,
Une piste peut-être ?
Voir le fichier:
http://www.cijoint.fr/cjlink.php?file=cj201101/cijfxaw3IM.zip
Le traitement de cinq années comportant 10000 lignes chacune prend
environ 17s (sur ma bécane)
Bon courage.
charabeuh ca marche
en fait
juste un beug sur les années 2007 car les "NON" sont en colonne P et non Q
"Charabeuh" a écrit dans le message de news:
ii75un$7on$Dans son message précédent, STEPH B a écrit :Bonjour
voici mon code
sub flk
Sheets("NON PAYEE TOTAL").Range("I3").Select
Selection.Copy
Range("J3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:úlse, Transpose:úlse
Sheets("NON PAYEE TOTAL").Range("I3") = ""
For I = Range("c65536").End(xlUp).Row To 7 Step -1
Cells(I, 1).EntireRow.Clear ' le 2 correspond au numero de colonne
Next
ligne = 2
With Sheets("2007")
For I = 1 To .[a65000].End(xlUp).Row
If .Cells(I, "P") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 13).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 17).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 25).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
With Sheets("2008")
For I = 1 To .[a65000].End(xlUp).Row
If .Cells(I, "Q") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 21).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 26).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
.Cells(I, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 11)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
With Sheets("2009")
For I = 1 To .[a65000].End(xlUp).Row
'MsgBox (.Cells(I, "Q"))
If .Cells(I, "Q") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 21).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 26).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
.Cells(I, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5,
11)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
With Sheets("2010")
For I = 1 To .[a65000].End(xlUp).Row
If .Cells(I, "Q") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 21).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
.Cells(I, 28).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5,
11)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
With Sheets("2011")
For I = 1 To .[a65000].End(xlUp).Row
If .Cells(I, "Q") = "NON" Then
.Cells(I, 1).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 1)
.Cells(I, 3).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 2)
.Cells(I, 14).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 3)
.Cells(I, 15).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 4)
.Cells(I, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 5)
.Cells(I, 18).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 6)
.Cells(I, 19).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 7)
.Cells(I, 20).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 8)
.Cells(I, 21).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 9)
.Cells(I, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5, 10)
.Cells(I, 28).Copy Sheets("NON PAYEE TOTAL").Cells(ligne + 5,
11)
Cells(ligne + 5, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
End If
Next
End With
End With
End With
End With
End With
end sub
Connaissez vous une version qui pourrait s'executer plus rapidement
sachant que mes feuilles 2007 - 2008 - 2009 - 2010 - 2011
contiennent environ 10000 lignes chacune?
merci d avance!
Bonsoir,
Une piste peut-être ?
Voir le fichier:
http://www.cijoint.fr/cjlink.php?file=cj201101/cijfxaw3IM.zip
Le traitement de cinq années comportant 10000 lignes chacune prend
environ 17s (sur ma bécane)
Bon courage.
charabeuh ca marche
en fait
juste un beug sur les années 2007 car les "NON" sont en colonne P et non Q
"Charabeuh" a écrit dans le message de news:
ii75un$7on$
charabeuh ca marche
en fait
juste un beug sur les années 2007 car les "NON" sont en colonne P et non Q
"Charabeuh" <please@feed.back> a écrit dans le message de news:
ii75un$7on$1@speranza.aioe.org...
charabeuh ca marche
en fait
juste un beug sur les années 2007 car les "NON" sont en colonne P et non Q
"Charabeuh" a écrit dans le message de news:
ii75un$7on$