Bonjour,
Je n'arrive pas à simplifier cette macro afin que quand je rajoute des lignes, celle -ci fonctionne toujours et ne s'arrête pas aux lignes 44 (ou 45 à la fin de la macro) mais respectivement 46 et 47 si je rajoute 2 lignes.
Une idée ?
Merci d'avance
JP
Sub Tri_JeanPiere()
'
' Tri_JeanPiere Macro
'
'
Range("D6").Select
Range(Selection, Selection.End(xlDown)).Select
Rows("6:44").Select
Range("D6").Activate
ActiveWorkbook.Worksheets("Liste JeanPiere").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Liste JeanPiere").Sort.SortFields.Add Key:=Range( _
"D644"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Liste JeanPiere").Sort
.SetRange Range("A6:L44")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("D6").Select
Sheets("Inputs").Select
Rows("6:7").Select
Selection.Copy
Sheets("Liste JeanPiere").Select
Rows("6:6").Select
Range("D6").Activate
Range(Selection, Selection.End(xlDown)).Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("A6").Select
Sheets("Inputs").Select
Range("A6").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Liste JeanPiere").Select
ActiveSheet.Paste
Range("D6").Select
Selection.End(xlDown).Select
Rows("45:45").Select
Range("D45").Activate
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.ClearContents
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Application.Goto Reference:="R1C1"
End Sub
Je n'arrive pas à simplifier cette macro afin que quand je rajoute des lignes, celle -ci fonctionne toujours et ne s'arrête pas aux lignes 44 (ou 45 à la fin de la macro) mais respectivement 46 et 47 si je rajoute 2 lignes.
Une idée ?
Merci d'avance
JP
Sub Tri_JeanPiere()
'
' Tri_JeanPiere Macro
'
'
Range("D6").Select
Range(Selection, Selection.End(xlDown)).Select
Rows("6:44").Select
Range("D6").Activate
ActiveWorkbook.Worksheets("Liste JeanPiere").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Liste JeanPiere").Sort.SortFields.Add Key:=Range( _
"D644"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Liste JeanPiere").Sort
.SetRange Range("A6:L44")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("D6").Select
Sheets("Inputs").Select
Rows("6:7").Select
Selection.Copy
Sheets("Liste JeanPiere").Select
Rows("6:6").Select
Range("D6").Activate
Range(Selection, Selection.End(xlDown)).Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("A6").Select
Sheets("Inputs").Select
Range("A6").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Liste JeanPiere").Select
ActiveSheet.Paste
Range("D6").Select
Selection.End(xlDown).Select
Rows("45:45").Select
Range("D45").Activate
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.ClearContents
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Application.Goto Reference:="R1C1"
End Sub