Set Active Cell to A1 on Open Q

  • Thread starter Thread starter Seanie
  • Start date Start date
S

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
 
change the inside of the for/next loop to this:

if sh.visible then
application.goto sh.range("A1"),true
end if
 
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
Click to expand...

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
 
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 -
Click to expand...

Thanks
 
Back
Top