Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Prevent A Macro From Running If SpreadSheet is Filtered | Excel Worksheet Functions | |||
automatically running a macro | Excel Discussion (Misc queries) | |||
How to stop getting the file save box when running a macro | Excel Discussion (Misc queries) | |||
CREATE MACRO TO COPY MULTIPLE WORKSHEETS | Excel Discussion (Misc queries) | |||
Shadows of the Pivottable Field List while macro is running | Excel Worksheet Functions |