Sub Bouton1_Clic()
' on suspend le rafraîchissement d'écran; plus rapide, moins mal aux yeux.
[COLOR=DarkGreen][B] Application.ScreenUpdating = False[/B][/COLOR]
'
If range("AD6") < 1 Or range("AD6") > 12 Then
MsgBox "Veuillez entrer un mois valide en AD6"
Exit Sub
End If
If range("AD8") < 2000 Then
MsgBox "Veuillez entrer une année valide en AD8"
Exit Sub
End If
Dim wda As Date, wdb As Date, nbj As Integer, annee As Integer, gwcel As range, btn As Shape
nbj = CDate("01/" & Format(range("AD6") + 1, "00") & "/" & Format(Year(Date), "0000")) - CDate("01/" & Format(range("AD6"), "00") & "/" & Format(Year(Date), "0000"))
For i = 1 To nbj
Sheets.Add after:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = Format(i, "00")
Sheets(1).Cells.Copy Destination:=Sheets(Sheets.Count).range("A1")
'mise en page
[B][COLOR=DarkGreen] Application.ExecuteExcel4Macro "PAGE.SETUP(,,0,0,0,0,,,0,,1,9,99,,,,,0,0)"[/COLOR][/B]
'
Sheets(Sheets.Count).range("AC6:AG17").Clear
For Each btn In Sheets(Sheets.Count).Shapes: btn.Delete: Next
Sheets(Sheets.Count).range("O1") = CDate(Format(i, "00") & "/" & Format(Sheets(1).range("AD6"), "00") & "/" & Format(Sheets(1).range("AD8"), "0000"))
Next i
Sheets.Add after:=Sheets(Sheets.Count)
'mise en page
[B][COLOR=DarkGreen] Application.ExecuteExcel4Macro "PAGE.SETUP(,,0,0,0,0,,,0,,1,9,99,,,,,0,0)"[/COLOR][/B]
'
With Sheets(Sheets.Count)
.Name = "Recap_01_14"
Sheets("Recap").Cells.Copy Destination:=.range("A1")
.range("A1") = "Récapitulatif du 1 au 14"
For Each gwcel In .range("A1:AA37")
If gwcel.Interior.ColorIndex = 43 Then ' N° de couleur à modifier en cas de changement de couleur de reference
gwcel(1).Formula = "=SUM(01:14!" & gwcel(1).Address & ")"
End If
Next
End With
With Sheets(Sheets.Count).Buttons.Add(320, 150, 200, 25)
.OnAction = "GW_lance_1a14"
.Characters.Text = "Lance la récapitulation"
.Locked = False
.LockedText = True
.Placement = xlFreeFloating
.PrintObject = False
End With
Sheets.Add after:=Sheets(Sheets.Count)
'mise en page
[B][COLOR=DarkGreen] Application.ExecuteExcel4Macro "PAGE.SETUP(,,0,0,0,0,,,0,,1,9,99,,,,,0,0)"[/COLOR][/B]
'
With Sheets(Sheets.Count)
.Name = "Recap_15_" & Format(i - 1, "00")
Sheets("Recap").Cells.Copy Destination:=.range("A1")
.range("A1") = "Récapitulatif du 15 au " & Format(i - 1, "00")
For Each gwcel In .range("A1:AA37")
If gwcel.Interior.ColorIndex = 43 Then ' N° de couleur à modifier en cas de changement de couleur de reference
gwcel(1).Formula = "=SUM(15:" & Format(i - 1, "00") & "!" & gwcel(1).Address & ")"
End If
Next
End With
With Sheets(Sheets.Count).Buttons.Add(320, 150, 200, 25)
.OnAction = "GW_lance_15afin"
.Characters.Text = "Lance la récapitulation"
.Locked = False
.LockedText = True
.Placement = xlFreeFloating
.PrintObject = False
End With
Sheets.Add after:=Sheets(Sheets.Count)
'mise en page
[B][COLOR=DarkGreen] Application.ExecuteExcel4Macro "PAGE.SETUP(,,0,0,0,0,,,0,,1,9,99,,,,,0,0)"[/COLOR][/B]
'
With Sheets(Sheets.Count)
.Name = "Recap_Mois" & Format(i - 1, "00")
Sheets("Recap").Cells.Copy Destination:=.range("A1")
.range("A1") = "Récapitulatif du 01 au " & Format(i - 1, "00")
For Each gwcel In .range("A1:AA37")
If gwcel.Interior.ColorIndex = 43 Then ' N° de couleur à modifier en cas de changement de couleur de reference
gwcel(1).Formula = "=SUM(01:" & Format(i - 1, "00") & "!" & gwcel(1).Address & ")"
End If
Next
End With
With Sheets(Sheets.Count).Buttons.Add(320, 150, 200, 25)
.OnAction = "GW_lance_mois"
.Characters.Text = "Lance la récapitulation"
.Locked = False
.LockedText = True
.Placement = xlFreeFloating
.PrintObject = False
End With
ThisWorkbook.SaveAs Filename:="CAISSE_" & Format(Sheets("Modele").range("AD8"), "0000") & "_" & Format(Sheets("Modele").range("AD6"), "00") & ".xls"
'on rétablie le rafraîchissement d'écran
[B][COLOR=DarkGreen] Application.ScreenUpdating = True[/COLOR][/B]
End Sub