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
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
|
Sub FilterMonthly()
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Display only the required number of months for the Pivottables
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Set the end date and start date for the display
'the last completed month to be considered as the end
Dim LastCompletedMonth As String
LastCompletedMonth = Right(Range("LastCompletedMonth").value, 2)
Dim LastMonth As Integer
LastMonth = CInt(LastCompletedMonth)
Dim RequiredNbr As Integer
' will be a variable later
RequiredNbr = 3
RequiredNbr = RequiredNbr - 1
'calculate the list of month to be displayed
Dim MonthList() As String
Dim PositiveMonth() As String
Dim NegativeMonth() As String
MaxPositiveMonth = LastMonth - 1
MaxNegativeMonth = LastMonth - RequiredNbr
'Check if there is months from the previous year
If MaxNegativeMonth <= 0 Then
For i = 0 To MaxPositiveMonth
Y = i + 1
If Y < 10 Then
PositiveMonth(i) = CStr(Year(Now())) + "-0" + CStr(Y)
Else
PositiveMonth(i) = CStr(Year(Now())) + "-" + CStr(Y)
End If
Next
For j = 0 To Abs(MaxNegativeMonth)
Z = 12 - j
If Z < 10 Then
NegativeMonth(j) = CStr(Year(Now()) - 1) + "-0" + CStr(Z)
Else
NegativeMonth(j) = CStr(Year(Now()) - 1) + "-" + CStr(Z)
End If
Next
For l = 0 To UBound(PositiveMonth)
MonthList(l) = PositiveMonth(l)
Next
For m = UBound(PositiveMonth) + 1 To UBound(PositiveMonth) + UBound(NegativeMonth)
MonthList(m) = NegativeMonth(m)
Next
Else
'all months are from the current year
For k = 0 To RequiredNbr
X = k + MaxNegativeMonth
If X < 10 Then
h = CStr(Year(Now())) + "-0" + CStr(X)
MonthList(k) = h
Else
MonthList(k) = CStr(Year(Now())) + "-" + CStr(X)
End If
Next
End If
Dim Result As String
For b = 0 To UBound(MonthList)
Result = Result + MonthList(b)
Next
Sheets("AT").Select
Range("N14").Select
ActiveCell.FormulaR1C1 = Result
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'List of Pivot tables to be updated for Incoming section
' Sheets("AT").Select
' Dim listptinc As Variant
' listptinc = Array("M-I-0", "M-I-2", "M-I-3", "M-I-4", "M-I-5", "M-I-6")
' For j = 0 To UBound(listptinc)
' With ActiveSheet.PivotTables(listptinc(j)).PivotFields("Creation Month")
' .PivotItems(OldMonthToBeRemoved).visible = False
' .PivotItems(NewMonthToBeDisplayed).visible = True
' End With
' Next
End Sub |
