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

Time:02-25

Whenever I open the worksheet, I wish the current Month name row to be adjusted at the top of work sheet. I have 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 it the current month Name row at the top whenever i open the this work sheet?

Hope you could help me in this regard

CodePudding user response:

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
  • Related