Search code examples
excelvba

View Current Month Name as the first Row at top of worksheet whenever it re-opens

Whenever I open the worksheet, I wish the current Month name row to be adjusted at the top of worksheet.

I entered month name in cell E2-January, then after 115 rows again cell E117-February and continue the naming with same interval. As it requires everyday entries and rechecks, the current month might be scrolled up or down.

Is it possible to have the current month Name row at the top whenever I open this worksheet?

Solution

  • Workbook_Open: Scroll to Current Month's Row

    • Copy the code into the ThisWorkbook module.
    • Whenever you open the workbook, it will select the worksheet and scroll accordingly.
    Option Explicit

Private Sub Workbook_Open()
    
    Const WorksheetName As String = "GA"
    Const RowOffset As Long = 115
    Const FirstCellAddress As String = "A2"
    
    Dim FirstCell As Range: Set FirstCell = Worksheets(WorksheetName) _
        .Range(FirstCellAddress).Offset((Month(Date) - 1) * RowOffset)
    
    Application.Goto FirstCell, True
    
End Sub
    • If you want to scroll whenever the worksheet is activated, then use the following in the sheet module Sheet2(GA):
    Option Explicit

Private Sub Worksheet_Activate()
    
    Const RowOffset As Long = 115
    Const FirstCellAddress As String = "A2"
    
    Dim FirstCell As Range: Set FirstCell =  _
        Range(FirstCellAddress).Offset((Month(Date) - 1) * RowOffset)
    
    Application.Goto FirstCell, True
    
End Sub