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 -- 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 |
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" <Craig_Richards.1tfx2h_1123495512.3228@excelforu m-nospam.com wrote in message news:Craig_Richards.1tfx2h_1123495512.3228@excelfo rum-nospam.com... 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 -- 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 |
All times are GMT +1. The time now is 11:44 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com