Bjr,
j'aimerai exécuter 2 macros (ci-joint) par une seule commande
que dois-je faire ???
éventuellement l'idéal serait de faire une compilation (2 en 1)(les variables sont en rouge)
merci
bye
-----------------------------------
Sub Equipe_100()
Application.ScreenUpdating = False
With Sheets('Equipe 1').Range('C2')
If InStr(1, .Value, ' ') < 1 Then Exit Sub
Nom = Left(.Value, InStr(1, .Value, ' ') + 1) + '.'
Nom = Application.WorksheetFunction.Proper(Nom)
End With
With Sheets(Nom)
Lig1 = .Range('A10000').End(xlUp).Row
Range(.Range('H' & Lig1 + 1), .Range('H' & Lig1 + 3)).Clear
End With
With Sheets('Equipe 1')
i = Application.CountA(.Range('D58'))
.Range('B5:I' & 4 + i).Copy
End With
With Sheets(Nom)
.Range('A65536').End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteFormats
.Range('A65536').End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Range(.Range('A4'), .Range('H' & Lig1)).Validation.Delete
Lig1 = .Range('A65536').End(xlUp).Row
Lig2 = .Range('J65536').End(xlUp).Row + 1
.Range('A4:H' & Lig1).Validation.Delete
Range(.Range('A4'), .Range('H' & Lig1)).Sort Key1:=.Range('A4'), Order1:=xlAscending
Range(.Range('J' & Lig2 - 1), .Range('M' & Lig2 - 1)).AutoFill _
Destination:=Range(.Range('J' & Lig2 - 1), .Range('M' & Lig1)), Type:=xlFillDefault
End With
Sheets('Equipe 1').Activate: Range('C2').Select
Application.ScreenUpdating = True
End Sub
-------------------------------------
Sub Equipe_101()
Application.ScreenUpdating = False
With Sheets('Equipe 1').Range('C9')
If InStr(1, .Value, ' ') < 1 Then Exit Sub
Nom = Left(.Value, InStr(1, .Value, ' ') + 1) + '.'
Nom = Application.WorksheetFunction.Proper(Nom)
End With
With Sheets(Nom)
Lig1 = .Range('A10000').End(xlUp).Row
Range(.Range('H' & Lig1 + 1), .Range('H' & Lig1 + 3)).Clear
End With
With Sheets('Equipe 1')
i = Application.CountA(.Range('D1214'))
.Range('B12:I' & 11 + i).Copy
End With
With Sheets(Nom)
.Range('A65536').End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteFormats
.Range('A65536').End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Range(.Range('A4'), .Range('H' & Lig1)).Validation.Delete
Lig1 = .Range('A65536').End(xlUp).Row
Lig2 = .Range('J65536').End(xlUp).Row + 1
.Range('A4:H' & Lig1).Validation.Delete
Range(.Range('A4'), .Range('H' & Lig1)).Sort Key1:=.Range('A4'), Order1:=xlAscending
Range(.Range('J' & Lig2 - 1), .Range('M' & Lig2 - 1)).AutoFill _
Destination:=Range(.Range('J' & Lig2 - 1), .Range('M' & Lig1)), Type:=xlFillDefault
End With
Sheets('Equipe 1').Activate: Range('C9').Select
Application.ScreenUpdating = True
End Sub
Message édité par: archi, à: 15/08/2005 12:30
j'aimerai exécuter 2 macros (ci-joint) par une seule commande
que dois-je faire ???
éventuellement l'idéal serait de faire une compilation (2 en 1)(les variables sont en rouge)
merci
bye
-----------------------------------
Sub Equipe_100()
Application.ScreenUpdating = False
With Sheets('Equipe 1').Range('C2')
If InStr(1, .Value, ' ') < 1 Then Exit Sub
Nom = Left(.Value, InStr(1, .Value, ' ') + 1) + '.'
Nom = Application.WorksheetFunction.Proper(Nom)
End With
With Sheets(Nom)
Lig1 = .Range('A10000').End(xlUp).Row
Range(.Range('H' & Lig1 + 1), .Range('H' & Lig1 + 3)).Clear
End With
With Sheets('Equipe 1')
i = Application.CountA(.Range('D58'))
.Range('B5:I' & 4 + i).Copy
End With
With Sheets(Nom)
.Range('A65536').End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteFormats
.Range('A65536').End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Range(.Range('A4'), .Range('H' & Lig1)).Validation.Delete
Lig1 = .Range('A65536').End(xlUp).Row
Lig2 = .Range('J65536').End(xlUp).Row + 1
.Range('A4:H' & Lig1).Validation.Delete
Range(.Range('A4'), .Range('H' & Lig1)).Sort Key1:=.Range('A4'), Order1:=xlAscending
Range(.Range('J' & Lig2 - 1), .Range('M' & Lig2 - 1)).AutoFill _
Destination:=Range(.Range('J' & Lig2 - 1), .Range('M' & Lig1)), Type:=xlFillDefault
End With
Sheets('Equipe 1').Activate: Range('C2').Select
Application.ScreenUpdating = True
End Sub
-------------------------------------
Sub Equipe_101()
Application.ScreenUpdating = False
With Sheets('Equipe 1').Range('C9')
If InStr(1, .Value, ' ') < 1 Then Exit Sub
Nom = Left(.Value, InStr(1, .Value, ' ') + 1) + '.'
Nom = Application.WorksheetFunction.Proper(Nom)
End With
With Sheets(Nom)
Lig1 = .Range('A10000').End(xlUp).Row
Range(.Range('H' & Lig1 + 1), .Range('H' & Lig1 + 3)).Clear
End With
With Sheets('Equipe 1')
i = Application.CountA(.Range('D1214'))
.Range('B12:I' & 11 + i).Copy
End With
With Sheets(Nom)
.Range('A65536').End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteFormats
.Range('A65536').End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Range(.Range('A4'), .Range('H' & Lig1)).Validation.Delete
Lig1 = .Range('A65536').End(xlUp).Row
Lig2 = .Range('J65536').End(xlUp).Row + 1
.Range('A4:H' & Lig1).Validation.Delete
Range(.Range('A4'), .Range('H' & Lig1)).Sort Key1:=.Range('A4'), Order1:=xlAscending
Range(.Range('J' & Lig2 - 1), .Range('M' & Lig2 - 1)).AutoFill _
Destination:=Range(.Range('J' & Lig2 - 1), .Range('M' & Lig1)), Type:=xlFillDefault
End With
Sheets('Equipe 1').Activate: Range('C9').Select
Application.ScreenUpdating = True
End Sub
Message édité par: archi, à: 15/08/2005 12:30