Automatically go to a cell when opening sheet
 

Being a novice, I depend on the "Masters" in the NG for answers. I
guess I figured out a macro that always goes to the cell I need to
start on in a sheet. My A & B columns are filled out to Row 550, but I
add data in column C.
I'm on Row 209 right now, so I wanted to go to the first blank cell in
column C. I went to the VBE, right clicked the sheet I wanted the
macro in, and used code below.

I posted this because I had a tough time finding a solution to what I
needed. My search keywords weren't doing the trick. Hope this helps
someone, if they need it.



Private Sub Worksheet_Activate()

Application.ScreenUpdating = False

' Macro that automatically goes to cell I needed. My A & B columns
have the years date
' and day filled out, and I work in Columns C thru BK, with many
hidden columns.
' I wanted a macro that went to the first cell I put data in, below
the row I'd
' already filled out. (Which is in column C)
' If your data starts in a different column, adjust the offsets
accordingly.


' Go to last row in Column C, (Excel 2007 version), use 65536 for
earlier versions.
Range("C1048576").Select


' Go to last used cell in column C
Selection.End(xlUp).Select


' Offset that cell up 13 Rows, 2 columns to left.
' This makes column A visible after the macro runs.
ActiveCell.Offset(-13, -2).Select


' Scrolls to that offset and makes it top, left viewable cell.
Application.Goto reference:=ActiveCell, Scroll:=True


' Offsets one more row than first offset did, then 2 columns.
' That puts the active cell near the center of the sheet
(vertically)
ActiveCell.Offset(14, 2).Select


End Sub

' You can make your active cell the top left cell on the sheet by
using
'ActiveCell.Offset(1, 0).Select
' without the single quote, instead of ActiveCell.Offset(-13,
-2).Select
' Then put a single quote in front of ActiveCell.Offset(14, 2).Select

Automatically go to a cell when opening sheet
 

Hi

When you turn screenupdating, always be sure to turn it on again. For this
purpose I wouldn't turn off screenupdating.

See my example:

Private Sub Worksheet_Activate()

TargetCol = "C"
LastRow = Rows.Count
Range(TargetCol & LastRow).End(xlUp).Offset(1, 0).Select

End Sub

Hopes it helps

---
Per

"Tim" skrev i meddelelsen
...
Being a novice, I depend on the "Masters" in the NG for answers. I
guess I figured out a macro that always goes to the cell I need to
start on in a sheet. My A & B columns are filled out to Row 550, but I
add data in column C.
I'm on Row 209 right now, so I wanted to go to the first blank cell in
column C. I went to the VBE, right clicked the sheet I wanted the
macro in, and used code below.

I posted this because I had a tough time finding a solution to what I
needed. My search keywords weren't doing the trick. Hope this helps
someone, if they need it.



Private Sub Worksheet_Activate()

Application.ScreenUpdating = False

' Macro that automatically goes to cell I needed. My A & B columns
have the years date
' and day filled out, and I work in Columns C thru BK, with many
hidden columns.
' I wanted a macro that went to the first cell I put data in, below
the row I'd
' already filled out. (Which is in column C)
' If your data starts in a different column, adjust the offsets
accordingly.


' Go to last row in Column C, (Excel 2007 version), use 65536 for
earlier versions.
Range("C1048576").Select


' Go to last used cell in column C
Selection.End(xlUp).Select


' Offset that cell up 13 Rows, 2 columns to left.
' This makes column A visible after the macro runs.
ActiveCell.Offset(-13, -2).Select


' Scrolls to that offset and makes it top, left viewable cell.
Application.Goto reference:=ActiveCell, Scroll:=True


' Offsets one more row than first offset did, then 2 columns.
' That puts the active cell near the center of the sheet
(vertically)
ActiveCell.Offset(14, 2).Select


End Sub

' You can make your active cell the top left cell on the sheet by
using
'ActiveCell.Offset(1, 0).Select
' without the single quote, instead of ActiveCell.Offset(-13,
-2).Select
' Then put a single quote in front of ActiveCell.Offset(14, 2).Select