How could I make the following code to filter months from the pivot table,when I select the year from the drop down box in the worksheet?
Code: Select all
Private Sub Worksheet_Change(ByVal Target As Range)
Dim d1 As Date
Dim d2 As Date
Dim m As Long
Dim rng As Range
Dim n As Long
Dim c As Long
' Act only if AK1 or AL1 has changed
If Not Intersect(Range("AK1,AL1"), Target) Is Nothing Then
Application.ScreenUpdating = False
m = Range("B10").CurrentRegion.Columns.Count
If Range("AL1") = "" Or Range("AK1") = "" Then
For c = 2 To m + 1
Cells(10, c).EntireColumn.Hidden = False
Next c
Else
' First day of the month
d1 = DateSerial(Worksheets("Stafflist").Range("YearList").Cells(Range("AL1")), Range("AK1"), 1)
' Last day of the month
d2 = DateSerial(Worksheets("Stafflist").Range("YearList").Cells(Range("AL1")), Range("AK1") + 1, 0)
' Last data row
' Date column
Set rng = Range(Cells(10, 2), Cells(10, m + 1))
' Count number of filtered records
n = Application.CountIfs(rng, ">=" & CLng(d1), rng, "<=" & CLng(d2))
' Test if no records
If n = 0 Then
' Inform user
MsgBox "There are no records for this month/year combination.", vbInformation
Else
' Hides rows for dates between first and last
For c = 2 To m + 1
Cells(10, c).EntireColumn.Hidden = Cells(10, c) < CLng(d1) Or Cells(10, c) > CLng(d2)
Next c
End If
End If
Application.ScreenUpdating = True
End If
End Sub
otherwise do I have to remove the line
d2 = DateSerial(Worksheets("Stafflist").Range("YearList").Cells(Range("AL1")), Range("AK1") + 1, 0)
to make the code do what I have asked above?
Any suggestion or help on this would be kindly appreciated.
