How to move to specific sections of a large worksheet.

  • Thread starter Thread starter BP
  • Start date Start date
B

BP

I'd like to know how to create macros to move from one location on a
worksheet to another, or from one worksheet to another, and have that
location centered on the screen. I can then link the macros to buttons. Does
anyone have a link to a step-by-step page for doing this?
TIA
 
BP,

If you use the Goto method with True as the 2nd parameter, you can put the
upper left cell of the range at the upper left of the active window.
Centering depends on a lot - you can't center ranges from the first few
columns, for example, so it usually isn't worth it.

Sub TryNow()
Application.Goto Range("NamedRange"), True
End Sub

Sub TryNow2()
Application.Goto Worksheets("Sheet1").Range("Z100"), True
End Sub

HTH,
Bernie
MS Excel MVP
 
You should check out "Custom Views".

Always comes up "centered", just as you created/saved the "view".

You can place a "Custom View" window on your toolbar, displaying the view
(name) currently in force, with the ability to expand, and display a
drop-down window, where you can click on any of the "saved" views, for
instant centered navigation.

You can even change grid line colors for emphasis of certain views, while
hiding specific columns and/or rows.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

I'd like to know how to create macros to move from one location on a
worksheet to another, or from one worksheet to another, and have that
location centered on the screen. I can then link the macros to buttons. Does
anyone have a link to a step-by-step page for doing this?
TIA
 
Thanks for all the tips. I will try them and see what's best for me and post
back.
Thanks, all.
 
I asked this question because of a problem I was having using named ranges
with the GOTO function linked to a button. Whenever I added rows to the
sheet the named range moved, but the GOTO did not. I needed a new approach.
I used the GOTO function to a single cell, top left on the screen and
"locked" it with the $ parameter, then used the TRUE parameter in the
function. Everything works perfectly. If I add rows the GOTO cell moves too.
I've deleted all the named ranges and the whole workbook is simpler.
Thanks all for your suggestions which were very helpful in arriving at this
solution.
 
Back
Top