1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43
| Sub Semaine()
Dim Plage As Range, L1 As Integer, L2 As Integer, Ligne As Long, S As Variant, D As Date
Dim Col As Integer, S1 As Double, S2 As Double, S3 As Double
With Sheets("Saisie")
'calcul du n° de semaine précédente
D = Int(Date)
S = DateSerial(Year(D + (8 - Weekday(D)) Mod 7 - 3), 1, 1)
S = ((D - S - 3 + (Weekday(S) + 1) Mod 7)) \ 7 + 1
If S = 53 And Month(Date) = 1 Then Exit Sub
S = S - 1
If S = 0 Then Exit Sub
'N° de ligne de la feuille Saisie hebdo
Ligne = Application.Match(S, ['Suivi hebdo'!A:A], 0)
'calcul de le plage hebdo sur la feuille Saisie
L1 = Application.Match(S, .[B:B], 0)
L1 = L1 - Weekday(.Cells(L1, 3), 2) + 1
If S = 1 Then L1 = 5
L2 = Application.Match(S, .[B:B], 0)
L2 = L2 + 7 - Weekday(.Cells(L2, 3), 2)
If L2 > 370 Then L2 = 370
i = S
'définition de la plage hebdo
Set Plage = .Range("B" & L1 & ":B" & L2)
With Sheets("Suivi hebdo")
'recopie des lignes15:17
With Sheets("Planning")
Col = Application.Match(S, .[7:7], 0)
Col = Col + (7 - Weekday(.Cells(8, Col))) * 2 + 1
If Col > 738 Then Col = 738
S1 = .Cells(15, Col)
S2 = .Cells(16, Col)
S3 = .Cells(17, Col)
End With
.Cells(Ligne, 9) = S1
.Cells(Ligne, "R") = S2
.Cells(Ligne, "AW") = S3
'recopies
.Cells(Ligne, 2) = Application.Sum(Plage.Offset(, 7))
.Cells(Ligne, 3) = Application.Sum(Plage.Offset(, 8))
'etc.
End With
End With
End Sub |
Partager