Sub BanksJumpEoM()
'
' BanksJumpEoM Macro
'
'
'To shift sheets for Call GoHome to work properly
Sheets("Client").Select
'BanksJump starts here
Sheets("Bank").Select
Call GoHome
Application.ScreenUpdating = True
Range("Banks[[#Headers],[Dt]]").Select
'To clear filter from Table Bank
'ActiveSheet.AutoFilterMode = False
'To Turn off AutoFilter before showing Filter Arrow
Dim tblBanks As ListObject
Set tblBanks = Range("Banks").ListObject
With tblBanks
'To make sure AutoFilter is turned on before proceeding
If .ShowAutoFilter = False Then
.Range.AutoFilter
End If
'To clear filter from Table Bank
.AutoFilter.ShowAllData
.ShowAutoFilterDropDown = False
.Sort.SortFields.Clear
End With
On Error Resume Next
ActiveSheet.ShowAllData
Err.Clear
ActiveWorkbook.Worksheets("Bank").ListObjects("Banks").Sort.SortFields.Clear
'To go to EoMonth in a filtered range
Dim ar, i As Long, j As Long, Dt As Long, ws As Worksheet, LRow As Long
Set ws = Worksheets("Bank")
LRow = ws.Cells(Rows.Count, "C").End(xlUp).Row
ar = ws.Range("C6", Cells(Rows.Count, "C").End(xlUp))
Dt = Evaluate("EOMonth(Today(), 0)+1")
For i = 1 To UBound(ar, 1)
If ar(i, 1) = CLng(Dt) And Cells(i + 5, 3).EntireRow.Hidden = False Then
Cells(i + 5, 3).Select
Exit Sub
ElseIf ar(i, 1) > CLng(Dt) Then
For j = 1 To (LRow - 1)
If Cells(i - j + 5, 3).EntireRow.Hidden = False Then
Cells(i - j + 5, 3).Select
Exit Sub
End If
Next j
End If
Next i
'To Hide/Show Filter Arrow
'ActiveSheet.AutoFilterMode = True
ActiveSheet.ListObjects("Banks").ShowAutoFilterDropDown = True
Selection.AutoFilter
End Sub