Running a macro for all visible worksheets in a workbook

Hello,

I'm not the most experienced of macro writers but can normally muddle through inelegantly enough. However this one has got me stuck. Can anyone help?

I am trying to run a macro for all visible worksheets in a workbook and failing gallantly. This is my code:

Sub update()

application.ScreenUpdating = False
Dim sht As Worksheet
For Each sht In ActiveWorkbook.Worksheets
If sht.Name = "End" Then GoTo finish
sht.Select
Range("A1").Select
RC = EssMenuVRetrieve()

Next sht
finish:
application.ScreenUpdating = True
End Sub

It updates the visible sheets fine but then I get run time error 1004 saying that Method 'select of object'_worksheet failed

Any ideas? I think I have probably not defined the final sheet correctly but I thought it worked that way.

I appreaciate any feedback you could hive me.

Thanks

Does this do it?

Sub update()
Application.ScreenUpdating = False
Dim sht As Worksheet
For Each sht In ActiveWorkbook.Worksheets
If sht.Visible = xlSheetVisible Then
If sht.Name <> "End" Then
sht.Select
Range("A1").Select
RC = EssMenuVRetrieve()
End If
End If
Next sht
finish:
Application.ScreenUpdating = True
End Sub

--
HTH

Bob Phillips

"Craig_Richards"
<[email protected]> wrote in
message news:[email protected]...
>
> Hello,
>
> I'm not the most experienced of macro writers but can normally muddle
> through inelegantly enough. However this one has got me stuck. Can
> anyone help?
>
> I am trying to run a macro for all visible worksheets in a workbook and
> failing gallantly. This is my code:
>
> Sub update()
>
> application.ScreenUpdating = False
> Dim sht As Worksheet
> For Each sht In ActiveWorkbook.Worksheets
> If sht.Name = "End" Then GoTo finish
> sht.Select
> Range("A1").Select
> RC = EssMenuVRetrieve()
>
> Next sht
> finish:
> application.ScreenUpdating = True
> End Sub
>
> It updates the visible sheets fine but then I get run time error 1004
> saying that Method 'select of object'_worksheet failed
>
> Any ideas? I think I have probably not defined the final sheet
> correctly but I thought it worked that way.
>
> I appreaciate any feedback you could hive me.
>
> Thanks
>
>
> --
> Craig_Richards
> ------------------------------------------------------------------------
> Craig_Richards's Profile:
http://www.excelforum.com/member.php...o&userid=21936
> View this thread: http://www.excelforum.com/showthread...hreadid=393813
>

Thanks for that. I'd tweaked my code a little in the meantime but the xlsheetvisible code was the key I needed.

Cheers