How to locate a cell in the absolute A1 position

[Jeff Smith]

Hi,
I have a workbook with about 10 spreadsheets. There are several named
ranges and each sheet is about 20 cols x up to 200 rows. Unless I
discipline myself to Ctrl+Home before leaving each sheet, the next time I
enter the sheet the cursor may be "anywhere" (i.e. where I was last).

I need to find a way to easily reorientate a sheet to get the AI in the top
left corner; (similarly for a named range).

Is there a way of inserting some code into the "navigation" macros so that
at each point od entry, it is controllable and predictable.

Thanking you in anticipation (you guys have been a massive help to my
learning and development),

Jeff Smith

 

[Tom Ogilvy]

go to the workbook event module (thisworkbook in the project explorer) and
us an event like this:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Application.Goto Sh.Range("A1"), True
End Sub

 

[Jeff Smith]

I have done this and am impressed by this code.

In the workbook there is a named range "MatlsCosts" that is located
Sheet!Materials!AB1:AX150. When I call for this range Excel typically shows
this data about the fourth column from the left. This is the only
unresolved query now - How do I get this range in the top left corner when I
navigate to it? (I don't want to lose the benefit of this new code below
which will now form the basis of most new preojects I start. Thanks very
much for this)

sincerely

Jeff Smith

 

[Tom Ogilvy]

Application.EnableEvents = False
Application.goto Reference:=range("MatlsCosts"), Scroll:=True
Application.EnableEvents = True