Set Active Cell to A1 on Open Q


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
 
Ad

Advertisements

A

AB

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

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

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
 
Ad

Advertisements

S

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top