Set Active Cell to A1 on Open Q
 

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

Set Active Cell to A1 on Open Q
 

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

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

On Feb 16, 10:13*am, Seanie wrote:
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

Set Active Cell to A1 on Open Q
 

on 2/16/2011, Seanie supposed :
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

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Set Active Cell to A1 on Open Q
 

On Feb 16, 11:00*am, GS wrote:
on 2/16/2011, Seanie supposed :





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

--
Garry

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

- Show quoted text -

Thanks