Re,
essaye ceci :
Public Nom As String
Sub transfertETsupprime()
' Macro par Dan pour Dubois - Martine le 13/02/08 - Excel pratique
Dim lig As Integer
Dim cel As Range
Dim ws As String
Application.ScreenUpdating = False
lig = 10
Nom = Range("2007!w6")
ws = Sheets("2007").Name
Sheets(ws).Select
Call testfeuille
For Each cel In Range("AI9", Range("AI65536").End(xlUp))
If cel.Value > 0 Then
With Sheets("modele")
.Cells(lig, 1) = Sheets(ws).Cells(cel.Row, 1)
.Cells(lig, 2) = Sheets(ws).Cells(cel.Row, 2)
.Cells(lig, 3) = Sheets(ws).Cells(cel.Row, 5)
.Cells(lig, 4) = Sheets(ws).Cells(cel.Row, 15) & "-" & Sheets(ws).Cells(cel.Row, 16) & "-" & Sheets(ws).Cells(cel.Row, 17)
.Cells(lig, 5) = Sheets(ws).Cells(cel.Row, 14)
.Cells(lig, 6) = Sheets(ws).Cells(cel.Row, 6)
.Cells(lig, 7) = Sheets(ws).Cells(cel.Row, 18)
.Cells(lig, 8) = Sheets(ws).Cells(cel.Row, 19)
.Cells(lig, 9) = Sheets(ws).Cells(cel.Row, 20)
.Cells(lig, 10) = Sheets(ws).Cells(cel.Row, 35)
.Cells(lig, 11) = Sheets(ws).Cells(cel.Row, 38)
.Cells(lig, 12) = Sheets(ws).Cells(cel.Row, 39)
.Cells(lig, 13) = Sheets(ws).Cells(cel.Row, 61)
.Cells(lig, 14) = Sheets(ws).Cells(cel.Row, 62)
.Cells(lig, 15) = Sheets(ws).Cells(cel.Row, 65)
.Cells(lig, 16) = Sheets(ws).Cells(cel.Row, 39)
.Cells(lig, 17) = Sheets(ws).Cells(cel.Row, 79)
End With
lig = lig + 1
End If
Next
Sheets("modele").Copy After:=Worksheets(Worksheets.Count) ''nouvel ongletMsgBox Nom
ActiveSheet.Name = Nom
Call purge
Sheets("2007").Select
End Sub
Sub purge()
Sheets("modele").Select
Range("A10", Range("R65536")).ClearContents
End Sub
Sub testfeuille()
For Each sh In Worksheets
If sh.Name = Nom Then End
Next
End Sub
C'est le code complet.
Remplace celui de ta macro par celui-ci
Dan