Starting Excel Worksheet at Top of Page

[Rutheen]

Hello,

I'm using Excel in 365 and I've created a workbook with 150 worksheets. These worksheets are of the same form which requires input of data in specified boxes. As I proceed from worksheet 1 to worksheet 2 etc. I'd like the sheet to be displayed with the top boxes showing. On many occasions the next worksheet will display the boxes in the middle of the form. It's tiresome having to continuously scroll to the top of the worksheet. Does anyone know a setting which will display subsequent worksheets with the top of the worksheet showing instead of the middle of the sheet? I'd appreciate any help that anyone can provide. Thanks. Rutheen.

 

[kevin9999]

It would be tiresome to set it up given you're talking about 150 worksheets, but this method will work. Set up a Worksheet_Activate event sub in each of the sheets. See Here to learn how this is done. The code in each and every sheet would be:

Private Sub Worksheet_Activate()
Application.Goto Me.Range("A1"), scroll:=1
End Sub

 

[Peter_SSs]

It would be tiresome to set it up given you're talking about 150 worksheets,

It certainly would.
Better to just have this in the ThisWorkbook module.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
  Application.Goto Range("A1"), Scroll:=True
End Sub

 

[kevin9999]

It certainly would.
Better to just have this in the ThisWorkbook module.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
  Application.Goto Range("A1"), Scroll:=True
End Sub

View attachment 53298

@Peter_SSs - Love it!

 

[Rutheen]

I thank all of you. I opened the Developer and placed the code in Visual Basic and in the Macro section and I have saved the workbook as Macro-Enabled. Upon opening the Workbook, I allow content and run the macro; however, pages still open in the middle. Any further considerations would be welcomed. Thanks. Rutheen.

 

[Candyman8019]

Rather than putting it in as a macro, put the code on each "sheet". Then, as you navigate to each sheet it will be triggered and take you to the top. I believe your spreadsheet will need to be saved as a macro-enabled workbook as well.

 

[Fluff]

I opened the Developer and placed the code in Visual Basic and in the Macro section and I have saved the workbook as Macro-Enabled.

Which of the two codes are you referring to?

 

[kevin9999]

@Rutheen both code suggestions work - as long as you put the code the the right place. In the VB Editor you need to double-click each sheet to place my code, or the workbook to insert Peter's (see post #3 for a visual). Having said that, Peter's suggestion is by far the most efficient way to go.

 

[Peter_SSs]

.. and definitely do not try to implement both suggestions in the same workbook.

To reiterate in relation to my code. In the vba window double-click where it says ThisWorkbook in the left pane and paste my code in the right hand pane.