Re : Problème dans ma macro !URGENT!
C'est noté, j'éviterai à l'avenir
Je ne peux malheureusement pas joindre mon fichier (politique de l'entreprise...)
Ci-dessous l'intégralité de ma macro (il y a surement plusieurs choses à revoir, je suis débutante et n'ai jamais pris de cours, mais le principal c'est qu'elle fonctionne) + une capture d'écran de ce à quoi ressemble mon fichier
Sub Suivi_à_la_commande()
'Bloquer l'affichage écran
Application.ScreenUpdating = False
'Bloquer les alertes
Application.DisplayAlerts = False
Range("E7").Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
Dim c As Integer
For c = 10000 To 1 Step -1
If Cells(c, 2) <> Cells(1, 2).Value Or Cells(c, 7) = "Stock" Then Rows(c).Delete
Next c
'Rajouter les semaines
Dim ws As Worksheet
For Each ws In Sheets
ws.Activate
Range("a1").Select
ActiveCell.FormulaR1C1 = "25/5/2015"
Range("a2").Select
Range("o1").Select
ActiveCell.FormulaR1C1 = "7"
Range("P1").Select
ActiveCell.FormulaR1C1 = "14"
Range("o1
1").Select
Selection.AutoFill Destination:=Range("O1:CM1"), Type:=xlFillDefault
Range("o1:CM1").Select
Range("n2").Select
ActiveCell.FormulaR1C1 = "=WEEKNUM(R1C1+R[-1]C,2)"
Range("n2").Select
Selection.AutoFill Destination:=Range("n2
2"), Type:=xlFillDefault
Range("n2
2").Select
Selection.AutoFill Destination:=Range("n2:CM2"), Type:=xlFillDefault
Range("n2:CM2").Select
Range("n3").Select
Rows("1:1").Select
With Selection.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
Rows("1:1").Select
Range("a1").Activate
Next ws
'Suppression des mises en formes toutes les feuilles
For Each ws In Sheets
ws.Activate
Cells.Select
Cells.FormatConditions.Delete
Range("A1").Select
Next ws
'Mise en forme semaine en cours
For Each ws In Sheets
ws.Activate
Range("J1").Select
ActiveCell.FormulaR1C1 = "=TODAY()"
Range("n2:CM2").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=NO.SEMAINE($J$1;2)=n$2"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0.249946592608417
End With
Selection.FormatConditions(1).StopIfTrue = False
Next ws
'Mise en forme lct retard
For Each ws In Sheets
ws.Activate
Range("H3:I13093").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$J3<$I3"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16777024
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Next ws
'Mise en forme planning
For Each ws In Sheets
ws.Activate
Range("M3:ET145").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=SI(ET($H3;$I3)="""";"""";SI(NO.SEMAINE($I3;2)<NO.SEMAINE($H3;2);ET((NO.SEMAINE($H3;2)<=n$2);(NO.SEMAINE($I3;2)<=n$2));ET((NO.SEMAINE($H3;2)<=n$2);(NO.SEMAINE($I3;2)>=n$2))))"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent1
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Range("A1").Select
Next ws
'Mise en forme niveau 0
For Each ws In Sheets
ws.Activate
Range("e3:e10000").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=ET($f3=0;$f3<>"""")"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent6
.TintAndShade = 0.399945066682943
End With
Selection.FormatConditions(1).StopIfTrue = False
Next ws
'Ajuster les colonnes
Dim k As Integer, l As Integer
k = Sheets.Count
For l = 1 To k
Sheets(l).Activate
Cells.Select
Cells.EntireColumn.AutoFit
Next l
'Réactivation des alertes
Application.DisplayAlerts = True
'Réactivation de l'écran
Application.ScreenUpdating = True
'Message de fin de traitement
MsgBox "Fini", 64, "Rapport"
End Sub