Code for positioning cursor in many sheets

[Sandy]

Hello!

I have a few workbooks that contain tab names that are
alphabetic (for five of the sheets) and the rest, which
can be x amount of sheets, are all numeric.

I want the ones with the numeric tabs to each have the
cursor position at E4 when the workbook is opened.

Is there a way to do this with code? I know I can specify
each sheet individually, but I need code that uses a
variable, like: If the tab is numeric, position the
cursor at E4.

Any help will be greatly appreciated!

Sandy

 

[Trevor Shuttleworth]

Sandy

one way of doing this:

Application.ScreenUpdating = False ' Stop the screen flickering
Sheets(Array("Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6")).Select
' Group the sheets
Range("E4").Select ' Select the start position for the sheets
Sheets("Sheet1").Select ' Remove the grouping by selecting another
sheet
Application.ScreenUpdating = True

Just switch on the macro recorder and do what you want to do manually.
Click on the first tab to be selected, shift and click on the last sheet to
be selected, select the cell you want to position at. Select the sheet
where you want to be when you've finished and switch off the recorder. You
should get something similar to the above (without the screen updating bit)

Regards

Trevor

 

[Don Guillett]

Right click on the excel icon to the left of FILE>view code>insert this>save
workbook.
I tested with a worksheet tab where 8 was the name

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If IsNumeric(ActiveSheet.Name) Then [e4].Activate
End Sub

 

[Tom Ogilvy]

Using the worksheet Activate event will position the cursor each time the
sheet is activated - this could be a major source of irritation to the user,
unless that it is your intent: that they be repositioned at E4 each time
they enter the sheet.

- If you only want this to occur when the workbook is opened put code in
the workbook_open event

Private Sub Workbook_Open()
Dim sh As Worksheet
Dim aSh As Worksheet
Set aSh = ActiveSheet
Application.ScreenUpdating = False
For Each sh In ThisWorkbook.Worksheets
If IsNumeric(sh.Name) Then
Application.Goto _
Reference:=sh.Range("A1"), _
Scroll:=True
sh.Range("E4").Select
End If
Next
Application.ScreenUpdating = True
aSh.Select
End Sub

This puts A1 as the upper left corner with E4 selected. If you want E4 in
the upper left corner and selected, then change A1 to E4 and delete the line
sh.Range("E4").Select


Note that using notation like [E4] is three times as slow as using
Range("E4")