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