Logo GNT
Actualités Tests & Guides Bons Plans
Twitter iPhone pliant OnePlus 11 PS5 Disney+ Orange Livebox Windows 11
Entraide Applications Microsoft Office Excel si oui cellule acceleration

si oui cellule acceleration

9 réponses
Avatar
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 _
:=False, Transpose:=False

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!
Signaler un problème avec ce contenu

9 réponses

Supprimer
Cette action est irreversible, confirmez la suppression du commentaire ?
Signaler le commentaire

Veuillez sélectionner un problème

Nudité
Violence
Harcèlement
Fraude
Vente illégale
Discours haineux
Terrorisme
Autre
Avatar
michdenis
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!
Avatar
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" 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!


Avatar
STEPH B
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!






Avatar
Jacky
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" a écrit dans le message de news:
4d46fd47$0$32467$
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!










Avatar
Charabeuh
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.
Avatar
STEPH B
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" 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.


Avatar
STEPH B
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.


Avatar
STEPH B
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" a écrit dans le message de news:
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" 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.






Avatar
Charabeuh
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" a écrit dans le message de news:
ii75un$7on$