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 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59
| Dim Idx_Ref As Variant
Dim NomMaj As String
Dim sem As Integer
Dim ann As Integer
Dim pivot As String
Dim i, j As Integer
sem = WorksheetFunction.WeekNum(Now)
ann = Sheets("Paramètres").Cells(2, 3).Value
Worksheets("EditionBilan").Visible = True
Worksheets("EditionBilan").Activate
Sheets("EditionBilan").Select
Application.Calculation = xlCalculationManual
If DateDiff("d", DateSerial(CInt(Sheets("Paramètres").Cells(3, 3).Value), 1, 1), Now) > 0 Then
sem = sem + WorksheetFunction.WeekNum(DateSerial(Sheets("Paramètres").Cells(2, 3).Value, 12, 25))
End If
With ActiveSheet.PivotTables("Mon_TCD").PivotFields("Semaines")
For i = 36 To sem
If i > 53 Then
j = i - 53
Else
j = i
End If
pivot = CStr(j) & " - " & ann
If j < 10 Then
pivot = "0" & CStr(j) & " - " & ann + 1
Else
If (j >= 10) And (j < 36) Then
pivot = CStr(j) & " - " & ann + 1
Else
If (ann = Year(Now)) Then
pivot = CStr(j) & " - " & ann + 1
End If
End If
End If
.PivotItems(pivot).Visible = True
Next
If ((j < 53) And (ann = Year(Now))) Then .PivotItems("53 - 2011").Visible = False
End With
Application.CommandBars("PivotTable").Visible = False
'
ActiveWorkbook.ShowPivotTableFieldList = False
ActiveSheet.PivotTables("Mon_TCD").PivotCache.Refresh
ThisWorkbook.EntrerLog Now(), "Demande situation"
Application.CommandBars("PivotTable").Visible = True
Application.Calculation = xlCalculationAutomatic
Worksheets("EditionBilan").Visible = True
Worksheets("EditionBilan").Activate |
Partager