VBA code for each visible sheet?

[jroo]

Hi - I'd like to create a macro that would select each visible sheet in a workbook and have the top left cell selected, so that users see the top of each tab.

I know this accomplishes going to the top of each sheet...
Application.Goto Reference:="R1C1"


I'm sure a "For Each" statement can do what I'd want, but I'm not sure how to tweak the coad below (note: over 20 tabs in my workbook, some of which are hidden/protected, so there's no need to run code on those tabs)

Sub ShowMiscTabs()
Application.ScreenUpdating = False
Dim sh
For Each sh In Sheets(Array("Sheet1", "Sheet2", "Sheet3, "Sheet4"))
sh.Visible = True
Next
Application.ScreenUpdating = True
End Sub

 

[njimack]

Dim ws as Worksheet
Application.ScreenUpdating = False
For Each ws in ThisWorkbook.Worksheets
If ws.Visible = True Then ws.Cells(1,1).Select
Next ws
Application.ScreenUpdating = True

 

[jroo]

Hi - I tried that but got this error "Select method of range class failed" ... when I clicked debug, it went to this line

If ws.Visible = True Then ws.Cells(1,1).Select

 

[njimack]

Hi - I tried that but got this error "Select method of range class failed" ... when I clicked debug, it went to this line

If ws.Visible = True Then ws.Cells(1,1).Select

Although you generally don't need to select a worksheet in VBA, sometimes you do have to. Try the version below:

Dim ws as Worksheet
Application.ScreenUpdating = False
For Each ws in ThisWorkbook.Worksheets
If ws.Visible = True Then 
ws.Select
Cells(1,1).Select
End If
Next ws
Application.ScreenUpdating = True

 

[BobUmlas]

Sub A1InAllSheets()
Dim tf As Boolean
tf = True
For Each sh In Sheets
If sh.Visible Then sh.Select tf
tf = False
Next
Range("A1").Select
ActiveSheet.Select
End Sub

 

[Smitty]

Why not use a WorkbookSheetActivate event? That would eliminate looping, and possibly having to repeat the code at some point.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Range("A1").Select
End Sub

HTH,

 

[BobUmlas]

Caution - that code would have a macro error if you activated a chart sheet.

 

[Smitty]

Thanks for the heads up Bob. (I generally don't bother with error handling in simple examples).

 

[jroo]

That worked... Thanks!