Open a worksheet at the top?

[Julie]

How can I ensure that my worksheets always open at the top? They all have a lot of vertical scroll
so it would be nice to "start at the begining".

Thanks,

Julie

 

[Dave Hawley]

Hi Julie

Riht click on the sheet name tab, select "View Code" and paste in this

Private Sub Worksheet_Activate()
Application.Goto Range("A1"), True
End Sub

***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****

 

[Norman Harker]

Hi Julie!

Here's a Workbook_BeforeClose subroutine:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.ScreenUpdating = False
Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
sh.Select
Application.Goto Reference:=sh.Range("a1"), Scroll:=True
Next sh
Application.ScreenUpdating = True
End Sub

This goes in the ThisWorkbook module

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

How can I ensure that my worksheets always open at the top? They

all have a lot of vertical scroll

 

[Dave Hawley]

Julie, if you want this for *all* Worksheets. Right click on the Excel
icon, top left next to "File" and select "View Code" and paste in this

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

***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****

 

[Julie]

Hi Julie!

Here's a Workbook_BeforeClose subroutine:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.ScreenUpdating = False
Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
sh.Select
Application.Goto Reference:=sh.Range("a1"), Scroll:=True
Next sh
Application.ScreenUpdating = True
End Sub

This goes in the ThisWorkbook module

Hi Norman,

Using your code I received the following error:
Method Select of Object '_Worksheet' failed

Julie

 

[Dave Peterson]

I like to put these kinds of things in the workbook_open event. Then each time
I open the workbook, it gets set up the way I like.

Norman used the _BeforeClose routine so that it does it when you're closing the
workbook--but that means you have to worry about whether the workbook should be
saved--maybe the user actually made errors and didn't want to save them.

Anyway, you can't select a sheet if it's hidden. And I'm guessing that you have
a hidden sheet.

This modification of Norman's code worked ok for me. (note the procedure was
changed, too.)

Option Explicit
Private Sub Workbook_Open()
Application.ScreenUpdating = False
Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
If sh.Visible Then
Application.Goto Reference:=sh.Range("a1"), Scroll:=True
End If
Next sh
Application.ScreenUpdating = True
End Sub