MASSJIPE
XLDnaute Impliqué
Bonjour,
Suite à une requête BO j'enregistre le fichier sous Excel (Pareto évolution)
Et je fais plusieurs manipulations de mise en forme mais pour éviter de faire cela tous les jours comment faire pour associer une macro ou macro complémentaire ou autre
Ci joint la macro
Merci
Sub Mise_en_forme()
'
' Mise_en_forme Macro
' Macro enregistrée le 10/04/2011 par Jean-Paul
'
'
Rows("1:3").Select
Selection.Delete Shift:=xlUp
Cells.Select
With Selection
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Font.ColorIndex = 0
Columns("B:B").Select
Selection.Delete Shift:=xlToLeft
Columns("G:G").Select
Selection.Delete Shift:=xlToLeft
ActiveWindow.SmallScroll Down:=-24
Cells.Select
Selection.Columns.AutoFit
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
Columns("U:U").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""A"""
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.ColorIndex = 5
End With
Selection.FormatConditions(1).Interior.Pattern = xlNone
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""B"""
With Selection.FormatConditions(2).Font
.Bold = True
.Italic = False
.ColorIndex = 46
End With
Columns("AA:AB").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""DD"""
Selection.FormatConditions(1).Interior.ColorIndex = 9
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""LONG"""
Selection.FormatConditions(2).Interior.ColorIndex = 27
Columns("AC:AC").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""EA4"""
Selection.FormatConditions(1).Interior.ColorIndex = 5
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""EA3"""
Selection.FormatConditions(2).Interior.ColorIndex = 46
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""EA2"""
Selection.FormatConditions(3).Interior.ColorIndex = 26
ActiveWindow.SmallScroll Down:=-18
ActiveWindow.ScrollColumn = 106
ActiveWindow.ScrollColumn = 105
ActiveWindow.ScrollColumn = 104
ActiveWindow.ScrollColumn = 103
ActiveWindow.ScrollColumn = 102
ActiveWindow.ScrollColumn = 100
ActiveWindow.ScrollColumn = 98
ActiveWindow.ScrollColumn = 96
ActiveWindow.ScrollColumn = 95
ActiveWindow.ScrollColumn = 93
ActiveWindow.ScrollColumn = 90
ActiveWindow.ScrollColumn = 87
ActiveWindow.ScrollColumn = 84
ActiveWindow.ScrollColumn = 81
ActiveWindow.ScrollColumn = 78
ActiveWindow.ScrollColumn = 76
ActiveWindow.ScrollColumn = 74
ActiveWindow.ScrollColumn = 72
ActiveWindow.ScrollColumn = 70
ActiveWindow.ScrollColumn = 67
ActiveWindow.ScrollColumn = 64
ActiveWindow.ScrollColumn = 62
ActiveWindow.ScrollColumn = 59
ActiveWindow.ScrollColumn = 56
ActiveWindow.ScrollColumn = 54
ActiveWindow.ScrollColumn = 52
ActiveWindow.ScrollColumn = 50
ActiveWindow.ScrollColumn = 47
ActiveWindow.ScrollColumn = 44
ActiveWindow.ScrollColumn = 40
ActiveWindow.ScrollColumn = 36
ActiveWindow.ScrollColumn = 33
ActiveWindow.ScrollColumn = 30
ActiveWindow.ScrollColumn = 29
ActiveWindow.ScrollColumn = 27
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 15
Range("Z1").Select
ActiveCell.FormulaR1C1 = "MOTEUR"
With ActiveCell.Characters(Start:=1, Length:=6).Font
.Name = "Arial"
.FontStyle = "Gras"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("AA1").Select
ActiveCell.FormulaR1C1 = "DIR"
With ActiveCell.Characters(Start:=1, Length:=3).Font
.Name = "Arial"
.FontStyle = "Gras"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("AB1").Select
ActiveCell.FormulaR1C1 = "TYPE"
With ActiveCell.Characters(Start:=1, Length:=4).Font
.Name = "Arial"
.FontStyle = "Gras"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("AC1").Select
ActiveCell.FormulaR1C1 = "EQUI"
With ActiveCell.Characters(Start:=1, Length:=4).Font
.Name = "Arial"
.FontStyle = "Gras"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("C2").Select
ActiveWindow.FreezePanes = True
Range("C1:AC1").Select
Selection.AutoFilter
End Sub
Suite à une requête BO j'enregistre le fichier sous Excel (Pareto évolution)
Et je fais plusieurs manipulations de mise en forme mais pour éviter de faire cela tous les jours comment faire pour associer une macro ou macro complémentaire ou autre
Ci joint la macro
Merci
Sub Mise_en_forme()
'
' Mise_en_forme Macro
' Macro enregistrée le 10/04/2011 par Jean-Paul
'
'
Rows("1:3").Select
Selection.Delete Shift:=xlUp
Cells.Select
With Selection
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Font.ColorIndex = 0
Columns("B:B").Select
Selection.Delete Shift:=xlToLeft
Columns("G:G").Select
Selection.Delete Shift:=xlToLeft
ActiveWindow.SmallScroll Down:=-24
Cells.Select
Selection.Columns.AutoFit
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
Columns("U:U").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""A"""
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.ColorIndex = 5
End With
Selection.FormatConditions(1).Interior.Pattern = xlNone
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""B"""
With Selection.FormatConditions(2).Font
.Bold = True
.Italic = False
.ColorIndex = 46
End With
Columns("AA:AB").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""DD"""
Selection.FormatConditions(1).Interior.ColorIndex = 9
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""LONG"""
Selection.FormatConditions(2).Interior.ColorIndex = 27
Columns("AC:AC").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""EA4"""
Selection.FormatConditions(1).Interior.ColorIndex = 5
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""EA3"""
Selection.FormatConditions(2).Interior.ColorIndex = 46
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""EA2"""
Selection.FormatConditions(3).Interior.ColorIndex = 26
ActiveWindow.SmallScroll Down:=-18
ActiveWindow.ScrollColumn = 106
ActiveWindow.ScrollColumn = 105
ActiveWindow.ScrollColumn = 104
ActiveWindow.ScrollColumn = 103
ActiveWindow.ScrollColumn = 102
ActiveWindow.ScrollColumn = 100
ActiveWindow.ScrollColumn = 98
ActiveWindow.ScrollColumn = 96
ActiveWindow.ScrollColumn = 95
ActiveWindow.ScrollColumn = 93
ActiveWindow.ScrollColumn = 90
ActiveWindow.ScrollColumn = 87
ActiveWindow.ScrollColumn = 84
ActiveWindow.ScrollColumn = 81
ActiveWindow.ScrollColumn = 78
ActiveWindow.ScrollColumn = 76
ActiveWindow.ScrollColumn = 74
ActiveWindow.ScrollColumn = 72
ActiveWindow.ScrollColumn = 70
ActiveWindow.ScrollColumn = 67
ActiveWindow.ScrollColumn = 64
ActiveWindow.ScrollColumn = 62
ActiveWindow.ScrollColumn = 59
ActiveWindow.ScrollColumn = 56
ActiveWindow.ScrollColumn = 54
ActiveWindow.ScrollColumn = 52
ActiveWindow.ScrollColumn = 50
ActiveWindow.ScrollColumn = 47
ActiveWindow.ScrollColumn = 44
ActiveWindow.ScrollColumn = 40
ActiveWindow.ScrollColumn = 36
ActiveWindow.ScrollColumn = 33
ActiveWindow.ScrollColumn = 30
ActiveWindow.ScrollColumn = 29
ActiveWindow.ScrollColumn = 27
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 15
Range("Z1").Select
ActiveCell.FormulaR1C1 = "MOTEUR"
With ActiveCell.Characters(Start:=1, Length:=6).Font
.Name = "Arial"
.FontStyle = "Gras"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("AA1").Select
ActiveCell.FormulaR1C1 = "DIR"
With ActiveCell.Characters(Start:=1, Length:=3).Font
.Name = "Arial"
.FontStyle = "Gras"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("AB1").Select
ActiveCell.FormulaR1C1 = "TYPE"
With ActiveCell.Characters(Start:=1, Length:=4).Font
.Name = "Arial"
.FontStyle = "Gras"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("AC1").Select
ActiveCell.FormulaR1C1 = "EQUI"
With ActiveCell.Characters(Start:=1, Length:=4).Font
.Name = "Arial"
.FontStyle = "Gras"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("C2").Select
ActiveWindow.FreezePanes = True
Range("C1:AC1").Select
Selection.AutoFilter
End Sub