Sub feuille1()
' test1a7 Macro
'
'pour créer une nouvelle feuille
Sheets("RAWDATA BRUT").Select
Sheets.Add
Range("A1").Select
'étapes 1 à 7
' /!\ la feuille créée doit s'appeller "feuil1"
Range("A1").Select
Sheets("RAWDATA BRUT").Select
Columns("K:K").Select
Selection.Copy
Sheets("Feuil1").Select
Columns("A:A").Select
ActiveSheet.Paste
Range("A1").Select
With Selection.Interior
.ColorIndex = 33
End With
Sheets("RAWDATA BRUT").Select
Columns("R:R").Select
Selection.Copy
Sheets("Feuil1").Select
Columns("B:B").Select
ActiveSheet.Paste
Columns("B:B").EntireColumn.AutoFit
Sheets("RAWDATA BRUT").Select
Columns("X:X").Select
Selection.Copy
Sheets("Feuil1").Select
Columns("C:C").Select
ActiveSheet.Paste
Range("D1").Select
With Selection.Interior
.ColorIndex = 16
End With
ActiveCell.FormulaR1C1 = "Dénominateur"
Range("D2").Select
Columns("D:D").ColumnWidth = 12.14
ActiveCell.FormulaR1C1 = "=IF(RC[-1]="""","""",1000)"
Range("D2").Select
Selection.AutoFill Destination:=Range("D2:D1000")
Range("D2:D1000").Select
Range("E1").Select
Sheets("RAWDATA BRUT").Select
ActiveWindow.SmallScroll ToRight:=6
Columns("X:X").Select
Selection.Copy
Sheets("Feuil1").Select
Columns("E:E").Select
ActiveSheet.Paste
Range("F1").Select
With Selection.Interior
.ColorIndex = 44
End With
ActiveCell.FormulaR1C1 = "Quantité en cours 2"
With ActiveCell.Characters(Start:=1, Length:=17).Font
.Name = "Arial"
End With
Range("F2").Select
Columns("F:F").EntireColumn.AutoFit
Range("F2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-3]="""","""",RC[-3]/RC[-2])"
Range("F2").Select
Selection.AutoFill Destination:=Range("F2:F1000")
Range("F2:F1000").Select
Sheets("RAWDATA BRUT").Select
Columns("W:W").Select
Selection.Copy
Sheets("Feuil1").Select
Columns("G:G").Select
ActiveSheet.Paste
Range("G1").Select
With Selection.Interior
.ColorIndex = 44
End With
ActiveCell.FormulaR1C1 = "UQ1"
Range("G2").Select
' étape 8: colonne stock
Range("H1").Select
With Selection.Interior
.ColorIndex = 44
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 1
End With
ActiveCell.FormulaR1C1 = "Stock"
Range("H2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(C[-7],'P:\DOSSIER ELODIE BLANCHET\Macro 09.2011\[CCNV.xls]Feuil1'!R2C8:R1000C10,3)"
Range("H2").Select
Selection.AutoFill Destination:=Range("H2:H1000")
Range("H2:H51").Select
'pour remplacer les #N/A par ""
Columns("H:J").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("F9").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = ""
Cells.Replace What:="#N/A", Replacement:="", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
'colonne stock sans doublons
Columns("I:I").Select
Selection.Insert Shift:=xlToRight
Range("I1").Select
ActiveCell.FormulaR1C1 = "Stock sans doublons"
Range("I2").Select
Columns("I:I").EntireColumn.AutoFit
Range("I2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]"
Range("I3").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]=R[-1]C[-1],"""",RC[-1])"
Selection.AutoFill Destination:=Range("I3:I10000")
Range("I3:I51").Select
Range("I3").Select
'pour remplacer les "" par des 0
Columns("I:I").Select
Selection.Find(What:="""""", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Selection.Replace What:="""""", Replacement:="0", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
'colonne RAP !!
Range("J1").Select
With Selection.Interior
.ColorIndex = 44
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
ActiveCell.FormulaR1C1 = "RAP"
Range("J2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-2]>RC[-4],"""",RC[-4]-RC[-1])"
Selection.AutoFill Destination:=Range("J2:J1000"), Type:=xlFillDefault
Range("J2:J1000").Select
'étapes 10 à 12 : dates
Sheets("RAWDATA BRUT").Select
ActiveWindow.SmallScroll ToRight:=8
Columns("AH:AH").Select
Selection.Copy
Sheets("Feuil1").Select
Columns("J:J").Select
ActiveSheet.Paste
Range("J1").Select
With Selection.Interior
.ColorIndex = 16
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 1
End With
ActiveCell.FormulaR1C1 = "Mois"
Range("K2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]="""","""",MONTH(RC[-1]))"
Range("K2").Select
Selection.AutoFill Destination:=Range("K2:K1000")
Range("K2:K1000").Select
Range("L1").Select
With Selection.Interior
.ColorIndex = 16
End With
ActiveCell.FormulaR1C1 = "Semaine"
Range("L2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-2]="""","""",WEEKNUM(RC[-2])-1)"
Range("L2").Select
Selection.AutoFill Destination:=Range("L2:L1000")
Range("L2:L5000").Select
Range("L7").Select
' étape 13: ajoute les colonnes F, I et J pour le TCD
Range("A1").Select
Sheets("RAWDATA BRUT").Select
Columns("F:F").Select
Selection.Copy
Sheets("Feuil1").Select
Columns("M:M").Select
ActiveSheet.Paste
Range("A1").Select
Sheets("RAWDATA BRUT").Select
Columns("I:I").Select
Selection.Copy
Sheets("Feuil1").Select
Columns("N:N").Select
ActiveSheet.Paste
Range("A1").Select
Sheets("RAWDATA BRUT").Select
Columns("J:J").Select
Selection.Copy
Sheets("Feuil1").Select
Columns("O:O").Select
ActiveSheet.Paste
Range("O1").Select
With Selection.Interior
.ColorIndex = 35
End With
Columns("O:O").EntireColumn.AutoFit
End Sub