si oui cellule acceleration

Le
STEPH B
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!
Vidéos High-Tech et Jeu Vidéo
Téléchargements
Vos réponses
Gagnez chaque mois un abonnement Premium avec GNT : Inscrivez-vous !
Trier par : date / pertinence
michdenis
Le #23071151
Bonjour,

Quelques suggestions, évidemment pas tester

A ) Impératif : débute par déclarer chacune de tes variables
et affecte-leur le bon type.
B ) Élimine autant que faire se peut, tous les "Select"
C ) Utilise un filtre automatique sur une colonne lorsque c'est possible
et boucle seulement sur les cellules visibles (qui répondent au critère)

D ) Passe en mode calcul manuel et désactive certaines propriétés

E ) Dans mon petit exemple, tu vas devoir trouver la relation qu'il y a
entre la variable "i" et "ligne" pour t'assurer que la copie se fait correctement.

F ) Est-ce que tu dois copier le format de cellule + la valeur à chaque occasion
sinon, pourquoi ne pas essayer "=" pour transmettre la valeur seulement
(le cas échéant, tu devras inverser les références
Sh.Cells(Ligne + 5, 1) = .Cells(i, 1)

F ) Tu répètes le principe pour chaque section...

'------------------------------------------------
Dim i As Long, Sh As Worksheet
Dim Ligne As Long, DerLig As Long

Dim ModCalcul As String

ModCalcul = Application.Calculation
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.ScreenUpdating = False

Set Sh = Sheets("NON PAYEE TOTAL")

With Sheets("NON PAYEE TOTAL")
.Range("J3") = .Range("I3")
.Range("I3") = ""
Ligne = .Range("c65536").End(xlUp).Row
.Range("C7:C" & Ligne).EntireRow.Clear
End With

Ligne = 2
With Sheets("2007")
DerLig = .[a65000].End(xlUp).Row
With .Range("P" & DerLig)
.AutoFilter field:=1, crieria1:="NON"
For Each c In .Range("_FilterDataBase").SpecialCells(xlCellTypeVisible)
i = c.Row
.Cells(i, 1).Copy Sh.Cells(Ligne + 5, 1)
.Cells(i, 3).Copy Sh.Cells(Ligne + 5, 2)
.Cells(i, 13).Copy Sh.Cells(Ligne + 5, 3)
.Cells(i, 14).Copy Sh.Cells(Ligne + 5, 4)
.Cells(i, 15).Copy Sh.Cells(Ligne + 5, 5)
.Cells(i, 17).Copy Sh.Cells(Ligne + 5, 6)
.Cells(i, 18).Copy Sh.Cells(Ligne + 5, 7)
.Cells(i, 19).Copy Sh.Cells(Ligne + 5, 8)
.Cells(i, 20).Copy Sh.Cells(Ligne + 5, 9)
.Cells(i, 25).Copy Sh.Cells(Ligne + 5, 10)
.Cells(Ligne + 5, 12) = "=YEAR(RC[-10])"
Ligne = Ligne + 1
Next
.Autofilter
End With
End With

Pour cette section de la macro :
.Cells(i, 13).Copy Sh.Cells(Ligne + 5, 3)
.Cells(i, 14).Copy Sh.Cells(Ligne + 5, 4)
.Cells(i, 15).Copy Sh.Cells(Ligne + 5, 5)
.Cells(i, 17).Copy Sh.Cells(Ligne + 5, 6)
.Cells(i, 18).Copy Sh.Cells(Ligne + 5, 7)
.Cells(i, 19).Copy Sh.Cells(Ligne + 5, 8)
.Cells(i, 20).Copy Sh.Cells(Ligne + 5, 9)

On pourrait peut-être remplacer cela par :

.Cells(i, 13).resize(,7) .Copy Sh.Cells(Ligne + 5, 3).resize(,7)
Ou
Sh.Cells(Ligne + 5, 3).resize(,7) = .Cells(i, 13).resize(,7)


à la fin de la macro :

Application.Calculation = ModCalcul
Application.EnableEvents = True
Application.ScreenUpdating = True


MichD
--------------------------------------------
"STEPH B" a écrit dans le message de groupe de discussion : 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!
Jacky
Le #23071501
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" 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!


STEPH B
Le #23072201
justement non tous les blocks ne sont pas identiques....

"Jacky" 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" 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!






Jacky
Le #23072741
Re

Les conditions sur les années sont peut-être à revoir
Tester ceci.....
'------------------------
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))
.Columns("P").AutoFilter Field:=1, Criteria1:="NON"
Set plage = .Range("_filterdatabase").Offset(1)
Set plage = plage.Resize(plage.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible)
.AutoFilterMode = False
For Each c In plage
x = 1
.Cells(c.Row, 1).Copy sh.Cells(ligne, x): x = x + 1
.Cells(c.Row, 3).Copy sh.Cells(ligne, x): x = x + 1
If Sheets(Trim(i)).Name = "2007" Then Sheets(Trim(i)).Cells(c.Row, 13).Copy sh.Cells(ligne, x): x =
x + 1
.Cells(c.Row, 14).Copy sh.Cells(ligne, x): x = x + 1
.Cells(c.Row, 15).Copy sh.Cells(ligne, x): x = x + 1
If Sheets(Trim(i)).Name > 2007 Then .Cells(c.Row, 16).Copy Sheets("NON PAYEE TOTAL").Cells(ligne,
x): x = x + 1
.Cells(c.Row, 17).Copy sh.Cells(ligne, x): x = x + 1
.Cells(c.Row, 18).Copy sh.Cells(ligne, x): x = x + 1
.Cells(c.Row, 19).Copy sh.Cells(ligne, x): x = x + 1
.Cells(c.Row, 20).Copy sh.Cells(ligne, x): x = x + 1
.Cells(c.Row, 25).Copy sh.Cells(ligne, x): x = x + 1
If .Name > 2007 And .Name < 2010 Then .Cells(c.Row, 26).Copy Sheets("NON PAYEE TOTAL").Cells(ligne,
x): x = x + 1
If .Name > 2007 Then .Cells(c.Row, 27).Copy Sheets("NON PAYEE TOTAL").Cells(ligne, x): x = x + 1
If .Name > 2008 Then .Cells(c.Row, 28).Copy Sheets("NON PAYEE TOTAL").Cells(ligne, x): x = x + 1
' .Cells(Ligne, 12) = "=YEAR(RC[-10])"
ligne = ligne + 1
Next
End With
Next
Application.Calculation = CalculActuel
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

--
Salutations
JJ


"STEPH B" 4d46fd47$0$32467$
justement non tous les blocks ne sont pas identiques....

"Jacky"
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" 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!










Charabeuh
Le #23072811
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.
STEPH B
Le #23074601
merci pour ton code qui marche" nikel sur ton exemple
mais sur mon fichier
ca plante là:
With Sheets("Aux")
Set MaCell = .Range("A1")
----> MaCell.Resize(MaZone.Rows.count, MaZone.Columns.count).Value =
MaZone.Value


"Charabeuh" 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.


STEPH B
Le #23074711
charabeuh ca marche
en fait

juste un beug sur les années 2007 car les "NON" sont en colonne P et non Q

"Charabeuh" 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.


STEPH B
Le #23074921
merci a tout le monde ca marche nikel.
après modifs et personalisation j ai trouvé la solution en m appuyant sur
celle de charabeuh

"STEPH B" 4d47e25c$0$32424$


charabeuh ca marche
en fait

juste un beug sur les années 2007 car les "NON" sont en colonne P et non Q

"Charabeuh" 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
Le #23077221
Bonsoir,

Merci du retour, Steph B.

Charabeuh




STEPH B avait soumis l'idée :

charabeuh ca marche
en fait

juste un beug sur les années 2007 car les "NON" sont en colonne P et non Q

"Charabeuh" ii75un$7on$
Publicité
Poster une réponse
Anonyme