Sheet selected, automatic return to top of sheet

[MIKEY]

Hi all.

Is there a formula that...When a sheet is selected via a macro button, you
are automatically returned to the top of the selected sheet...or, when you
open your workbook the next day all sheets are returned to the top.

If this is possible I would need sheets returned to Row 8, as Rows 1-7 are
frozen

Thanks in advance
Mike

 

[Stefi]

You need these two event sub for it:

Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Activate
ws.Range("A8").Select
Next ws
Worksheets(1).Activate
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Sh.Range("A8").Select
End Sub


Post if you need help to install them!

--
Regards!
Stefi



„MIKEY†ezt írta:

 

[MIKEY]

I went to install in WorkBook and i get ..........Compile error:
Ambiguous name
detected:Workbook_Open

 

[Dave Peterson]

You only get one Workbook_Open event per workbook.

You'll have to merge the procedures into one.

If you need help, you should post both procedures in your follow-up.

 

[MIKEY]

Thanks Stefi

I used the first formula and all my sheets return to the beginning. I have
some sheets that are Frozen in screen view and they don't move. How do I
omit these from the formula.

There are 35 sheets I would like to omit and I guess I will need to list
these. The sheets are not in numerical order

Regards
Mike

 

[Stefi]

Sorry, I couldn't find out what you mean on "Frozen in screen view", please
clarify it. Isn't there any common properties of sheets to be omitted?

--
Regards!
Stefi



„MIKEY†ezt írta:

 

[MIKEY]

You can't scroll down or across i.e. freeze pane
Common properties for sheets to be omitted...they are 'help' sheets for the
users

Cheers Mike
..

 

[Stefi]

Try these:

Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Activate
If Not ActiveWindow.FreezePanes Then _
ws.Range("A8").Select
Next ws
Worksheets(1).Activate
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Not ActiveWindow.FreezePanes Then _
Sh.Range("A8").Select
End Sub



--
Regards!
Stefi



„MIKEY†ezt írta: