Set Active Cell to A1 on Open Q

[Seanie]

I have the code below which moves all sheets active cell to A1, but
how do I tweak to also do so when I have hidden worksheets? Currently
it debugs as I have some sheets hidden

Private Sub Workbook_Open()
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
ThisWorkbook.Sheets("Month").Select
Application.ScreenUpdating = True


End Sub

 

[AB]

change the inside of the for/next loop to this:

if sh.visible then
application.goto sh.range("A1"),true
end if

 

[GS]

I have the code below which moves all sheets active cell to A1, but
how do I tweak to also do so when I have hidden worksheets? Currently
it debugs as I have some sheets hidden

Private Sub Workbook_Open()
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
ThisWorkbook.Sheets("Month").Select
Application.ScreenUpdating = True


End Sub

Don't select the sheets.

Private Sub Workbook_Open()
Dim sh As Worksheet
With Application
.ScreenUpdating = False
For Each sh In ThisWorkbook.Worksheets
If sh.Visible Then
.Goto Reference:=sh.Range("a1"), Scroll:=True
Else
sh.Visible = xlSheetVisible
.Goto Reference:=sh.Range("a1"), Scroll:=True
sh.Visible = xlSheetHidden
End If
Next sh
.ScreenUpdating = True
End With '//Application
ThisWorkbook.Sheets("Month").Select
End Sub

 

[Seanie]

Don't select the sheets.

  Private Sub Workbook_Open()
    Dim sh As Worksheet
    With Application
     .ScreenUpdating = False
      For Each sh In ThisWorkbook.Worksheets
        If sh.Visible Then
          .Goto Reference:=sh.Range("a1"), Scroll:=True
        Else
          sh.Visible = xlSheetVisible
          .Goto Reference:=sh.Range("a1"), Scroll:=True
          sh.Visible = xlSheetHidden
        End If
      Next sh
      .ScreenUpdating = True
    End With '//Application
    ThisWorkbook.Sheets("Month").Select
  End Sub

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text -

- Show quoted text -

Thanks