Hidden Worksheets at Start-up of File Q

[John]

All

I have the following code which fires on opening of my workbook, problem is
I am getting an 'object defined' error. I think its because I have hidden
worksheets within the workbook, well at least it worked when I had none of
the sheets hidden. How can I work-around this leaving the worksheets hidden?

Thanks


Private Sub Workbook_Open()
Application.ScreenUpdating = False
Dim sh As Worksheet


Sheets("Sales Mix").Select
Cells.Select
Selection.ClearContents
Range("A1").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Range("A1").Select
Application.ScreenUpdating = False

With Application
.Calculation = xlManual
.MaxChange = 0.001
End With

Sheets("Sales Mix").Select

Range("A1").Select

With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False
Application.ScreenUpdating = True


For Each sh In ThisWorkbook.Worksheets
sh.Select
Application.Goto Reference:=sh.Range("a1"), Scroll:=True
Next sh
ThisWorkbook.Sheets("Home").Select
Application.ScreenUpdating = True


End Sub

 

[Bob Phillips]

Try this

For Each sh In ThisWorkbook.Worksheets
shStatus = sh.Visible
sh.Visible = True
sh.Select
Application.Goto Reference:=sh.Range("a1"), Scroll:=True
sh.Visible = shStatus
Next sh


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Arvi Laanemets]

Hi

Another option: Rewrite the code so you don't select or activate anything (a
bonus is, that such code works usually faster, and the display on the screen
isn't so hectic) . Something like (on fly, you have to fin'd right syntax
yourself):

....
Worksheets("SalesMix").UsedRange.ClearContents
Workssheets("SalesMix").QueryTables(1).Refresh BackgroundQuery:=False
....


Arvi Laanemets

 

[John]

Thanks guys

Hi

Another option: Rewrite the code so you don't select or activate anything
(a
bonus is, that such code works usually faster, and the display on the
screen
isn't so hectic) . Something like (on fly, you have to fin'd right syntax
yourself):

...
Worksheets("SalesMix").UsedRange.ClearContents
Workssheets("SalesMix").QueryTables(1).Refresh BackgroundQuery:=False
...


Arvi Laanemets