Move to specific cell and position window so user can see it.

[twfme57]

I have a worksheet with two sheets. I use macros that goes to specific cells in sheet 2 but when going to cells below where the window can see them, the user can't tell that they've 'arrived' at the correct cell. For instance, when they click the button to go to cell J255 in sheet2, the window isn't in the correct position to show it. Any ideas? Sorry if this has already been asked. TIA!

 

[twfme57]

I tested the code on a dummy workbook and it worked as you requested. It is hard to work with pictures. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screenshots (not pictures) of your sheets. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

Got it - here is a link to the file in dropbox. Thanks! Dropbox

 

[jolivanes]

If it does not need to be centered in the window.
Change references as required

With Sheets("Sheet3")
        .Activate
        .Range("AA75").Select
    End With
    With ActiveWindow
        .ScrollRow = ActiveCell.Row - 20
        .ScrollColumn = ActiveCell.Column - 15
    End With

Change the -20 and -15 to half the rows/columns visible on your screen to have it as close to center as possible.

 

[mumps]

The code seems to work for me. The desired cell is displayed at the top of the screen and the row is highlighted.

 

[FormR]

The code I suggested also appears to work from my end with your sample file, although it just jumps to the specific cell rather than the whole column (I'm not sure where that requirement came from?)

One note though, in the code for the first button on the sample file, you have commented out the line that selects the BF-1 sheet.

Sub Meters_Sockets()
'
' Meters_Sockets Macro
'    Sheets("BF-1").Select
     Range("J3").Select
    Application.Goto ActiveCell.EntireRow, True
End Sub

 

[Rhodie72]

This is another way

Sub Jump_to_Cell()
'
' Jump_to_Cell Macro
'

'
Dim MyRange

    MyRange = InputBox("Write range or Cell you want to see", "Select Cell(s)", "A1")
    Application.Goto Reference:=Chr(34) & MyRange & Chr(34)
    With ActiveWindow
        .Zoom = True
        .Zoom = 100
    End With
End Sub

 

[twfme57]

This is another way

Sub Jump_to_Cell()
'
' Jump_to_Cell Macro
'

'
Dim MyRange

    MyRange = InputBox("Write range or Cell you want to see", "Select Cell(s)", "A1")
    Application.Goto Reference:=Chr(34) & MyRange & Chr(34)
    With ActiveWindow
        .Zoom = True
        .Zoom = 100
    End With
End Sub

Thank you

This is another way

Sub Jump_to_Cell()
'
' Jump_to_Cell Macro
'

'
Dim MyRange

    MyRange = InputBox("Write range or Cell you want to see", "Select Cell(s)", "A1")
    Application.Goto Reference:=Chr(34) & MyRange & Chr(34)
    With ActiveWindow
        .Zoom = True
        .Zoom = 100
    End With
End Sub

 

[Rhodie72]

Thank you

You're welcome