Simplification code macro
s
Bonjour Forum,
Serait-il possible de m'aider à simplifier le code de la macro ? En fait, je pense qu'il y a moyen d'avoir un code plus épuré et lisible que la version actuelle.
'Création de la feuille Analyse
Sheets("Sheet1").Select
Sheets("Sheet1").Copy Before:=Sheets(1)
Sheets("Sheet1 (2)").Select
Sheets("Sheet1 (2)").Name = "Analyse"
'Suppression des lignes d'entête 1 à 8 provenant de InfoRH
Rows("1:8").Select
Selection.Delete Shift:=xlUp
Range("A65536").End(xlUp).Select
Selection.Delete Shift:=xlUp
Range("A65536").End(xlUp).Select
Selection.Delete Shift:=xlUp
Columns("O:P").Select
Selection.Delete Shift:=xlToLeft
Columns("P:P").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Delete Shift:=xlToLeft
'Ajout / nomme des colonnes pour l'analyse des candidatures et mise en forme
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Base"
Sheets("Sheet2").Select
Sheets("Sheet2").Name = "Étape Réussie"
Sheets("Sheet3").Select
Sheets("Sheet3").Name = "Admissibilité"
Sheets("Sheet4").Select
Sheets("Sheet4").Name = "Éligibilité"
Sheets("Analyse").Select
Range("O1").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.ReadingOrder = xlContext
.MergeCells = False
End With
ActiveCell.FormulaR1C1 = "Pmed?"
With ActiveCell.Characters(Start:=1, Length:=5).Font
.Name = "ARIAL"
.FontStyle = "Gras"
.Size = 10
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
End With
Range("P1").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.ReadingOrder = xlContext
.MergeCells = False
End With
ActiveCell.FormulaR1C1 = "'12"
With ActiveCell.Characters(Start:=1, Length:=2).Font
.Name = "ARIAL"
.FontStyle = "Gras"
.Size = 10
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
End With
Range("Q1").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.ReadingOrder = xlContext
.MergeCells = False
End With
ActiveCell.FormulaR1C1 = "P%?"
With ActiveCell.Characters(Start:=1, Length:=3).Font
.Name = "ARIAL"
.FontStyle = "Gras"
.Size = 10
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
End With
Range("R1").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.ReadingOrder = xlContext
.MergeCells = False
End With
ActiveCell.FormulaR1C1 = "T%?"
With ActiveCell.Characters(Start:=1, Length:=3).Font
.Name = "ARIAL"
.FontStyle = "Gras"
.Size = 10
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
End With
Range("S1").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.ReadingOrder = xlContext
.MergeCells = False
End With
ActiveCell.FormulaR1C1 = "recherchev(stxt("
With ActiveCell.Characters(Start:=1, Length:=16).Font
.Name = "ARIAL"
.FontStyle = "Gras"
.Size = 10
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
End With
'Défini le nombre de ligne à considérer dans l'onglet Éligibilité
Sheets("Éligibilité").Select
ComptElig = ActiveSheet.UsedRange.Rows.Count - 2
Range("G8").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.ReadingOrder = xlContext
.MergeCells = False
End With
ActiveCell.FormulaR1C1 = "Concatener"
With ActiveCell.Characters(Start:=1, Length:=10).Font
.Name = "Arial"
.FontStyle = "Gras"
.Size = 10
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
End With
'Macro Bordures tableau Analyse - Mous
Sheets("Analyse").Select
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
With Selection
.VerticalAlignment = xlCenter
.ReadingOrder = xlContext
End With
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
End With
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.ReadingOrder = xlContext
End With
Je vous remercie amplement.
Bonjour,
voici des exemples à essayer, est ce que ça convient ?
'Création de la feuille Analyse
Sheets("Sheet1").Copy Before:=Sheets(1)
ActiveSheet.Name = "Analyse"
'Suppression des lignes d'entête 1 à 8 provenant de InfoRH
Rows("1:8").Delete Shift:=xlUp
'Ajout / nomme des colonnes pour l'analyse des candidatures et mise en forme
Sheets("Sheet1").Name = "Base"
Sheets("Sheet2").Name = "Étape Réussie"
Sheets("Sheet3").Name = "Admissibilité"
Sheets("Sheet4").Name = "Éligibilité"
addr = Array("O1", "P1", "Q1", "R1", "S1")
vl = Array("Pmed?", "'12", "P%?", "T%?", "recherchev(stxt(")
With Sheets("Analyse")
For i = LBound(addr) To UBound(addr)
With .Range(addr(i))
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.ReadingOrder = xlContext
.MergeCells = False
.FormulaR1C1 = vl(i)
With .Font
.Name = "ARIAL"
.FontStyle = "Gras"
.Size = 10
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
End With
End With
Next
End With
'Macro Bordures tableau Analyse - Mous
With Sheets("Analyse")
With .Range("A1").CurrentRegion
.VerticalAlignment = xlCenter
.ReadingOrder = xlContext
For i = 1 To .Borders.Count - 2
With .Borders(i)
.LineStyle = xlContinuous
.Weight = xlThin
End With
Next
End With
With .Range("A1:" & Cells(1, Columns.Count).End(xlToLeft).Address)
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.ReadingOrder = xlContext
End With
End With
s
Bonjour sabV,
Tout d'abord, merci pour l'intérêt porté à l'égard de mon sujet.
Pour répondre à ta question, quand je remplace le code nommé
par ce que tu m'as proposé, la macro perd le nord'Suppression des lignes d'entête 1 à 8 provenant de InfoRH
En fait, cette macro est plus longue vu qu'elle a été faite via l'enregistreur de macro.
Voici le code complet: