Worksheet(s) open to last used row
 

Greetings, I have a workbook with 50+ worksheets in it. Each day when I
launch my workbook I have to scroll down to the last few visible rows to be
able to enter in the new data for the day. Every time, 50 times... It's
getting a little tedious. Is there a way to code the worksheet/workbook so
that the worksheet opens up so that the last two or three filled rows are
visible at the top of the worksheet?

(I keep thinking, "If it default opens to the first row, surely there is a
way to get it to default open to the "x" row...")
Thanks,

Worksheet(s) open to last used row
 

Hi,

Alt + F11 to open VB editor. Doubleclick 'ThisWorkbook' and paste this in on
the right. Each time you select a sheet it will leave 4 rows of the ised
range at the top

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
On Error Resume Next

LastRow = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row

With ActiveWindow
..ScrollRow = LastRow - 3
End With
End Sub


Mike

"PapaBear" wrote:

Greetings, I have a workbook with 50+ worksheets in it. Each day when I
launch my workbook I have to scroll down to the last few visible rows to be
able to enter in the new data for the day. Every time, 50 times... It's
getting a little tedious. Is there a way to code the worksheet/workbook so
that the worksheet opens up so that the last two or three filled rows are
visible at the top of the worksheet?

(I keep thinking, "If it default opens to the first row, surely there is a
way to get it to default open to the "x" row...")
Thanks,

Worksheet(s) open to last used row
 

Thanks Mike - it worked perfectly. However, it uncovered a detail that I had
forgotten so I did not mention earlier. I currently use columns "A" through
"H" for my regular entries, way down in column "AA" I had added a row with a
formula using the "COUTNBLANKS" function and some command buttons to use as
an error checking alert to make sure that nobody forgets to fill in the
cells. (I have the fonts in this column as "white" so that you can't see
them, which is why I forgot about them). This column is prefilled all the
way to row 400, so as you probably have guessed, your code is tripping on
this column. Is there a way to limit the rows scanned to just "A:H" so that
it will disregard column "AA"? Thanks again for your help. PB

"Mike H" wrote:

Hi,

Alt + F11 to open VB editor. Doubleclick 'ThisWorkbook' and paste this in on
the right. Each time you select a sheet it will leave 4 rows of the ised
range at the top

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
On Error Resume Next

LastRow = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row

With ActiveWindow
.ScrollRow = LastRow - 3
End With
End Sub


Mike

"PapaBear" wrote:

Greetings, I have a workbook with 50+ worksheets in it. Each day when I
launch my workbook I have to scroll down to the last few visible rows to be
able to enter in the new data for the day. Every time, 50 times... It's
getting a little tedious. Is there a way to code the worksheet/workbook so
that the worksheet opens up so that the last two or three filled rows are
visible at the top of the worksheet?

(I keep thinking, "If it default opens to the first row, surely there is a
way to get it to default open to the "x" row...")
Thanks,

Worksheet(s) open to last used row
 

Wow! perfect, works like a champ - thanks for the help.

"Mike H" wrote:

Hi,

Just use column A

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
On Error Resume Next
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
With ActiveWindow
.ScrollRow = LastRow - 3
End With
End Sub


Mike

"PapaBear" wrote:

Thanks Mike - it worked perfectly. However, it uncovered a detail that I had
forgotten so I did not mention earlier. I currently use columns "A" through
"H" for my regular entries, way down in column "AA" I had added a row with a
formula using the "COUTNBLANKS" function and some command buttons to use as
an error checking alert to make sure that nobody forgets to fill in the
cells. (I have the fonts in this column as "white" so that you can't see
them, which is why I forgot about them). This column is prefilled all the
way to row 400, so as you probably have guessed, your code is tripping on
this column. Is there a way to limit the rows scanned to just "A:H" so that
it will disregard column "AA"? Thanks again for your help. PB

"Mike H" wrote:

Hi,

Alt + F11 to open VB editor. Doubleclick 'ThisWorkbook' and paste this in on
the right. Each time you select a sheet it will leave 4 rows of the ised
range at the top

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
On Error Resume Next

LastRow = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row

With ActiveWindow
.ScrollRow = LastRow - 3
End With
End Sub


Mike

"PapaBear" wrote:

Greetings, I have a workbook with 50+ worksheets in it. Each day when I
launch my workbook I have to scroll down to the last few visible rows to be
able to enter in the new data for the day. Every time, 50 times... It's
getting a little tedious. Is there a way to code the worksheet/workbook so
that the worksheet opens up so that the last two or three filled rows are
visible at the top of the worksheet?

(I keep thinking, "If it default opens to the first row, surely there is a
way to get it to default open to the "x" row...")
Thanks,

Worksheet(s) open to last used row
 

Your welcome, I'm glad that helped and thanks for the feedback

Mike

"PapaBear" wrote:

Wow! perfect, works like a champ - thanks for the help.

"Mike H" wrote:

Hi,

Just use column A

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
On Error Resume Next
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
With ActiveWindow
.ScrollRow = LastRow - 3
End With
End Sub


Mike

"PapaBear" wrote:

Thanks Mike - it worked perfectly. However, it uncovered a detail that I had
forgotten so I did not mention earlier. I currently use columns "A" through
"H" for my regular entries, way down in column "AA" I had added a row with a
formula using the "COUTNBLANKS" function and some command buttons to use as
an error checking alert to make sure that nobody forgets to fill in the
cells. (I have the fonts in this column as "white" so that you can't see
them, which is why I forgot about them). This column is prefilled all the
way to row 400, so as you probably have guessed, your code is tripping on
this column. Is there a way to limit the rows scanned to just "A:H" so that
it will disregard column "AA"? Thanks again for your help. PB

"Mike H" wrote:

Hi,

Alt + F11 to open VB editor. Doubleclick 'ThisWorkbook' and paste this in on
the right. Each time you select a sheet it will leave 4 rows of the ised
range at the top

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
On Error Resume Next

LastRow = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row

With ActiveWindow
.ScrollRow = LastRow - 3
End With
End Sub


Mike

"PapaBear" wrote:

Greetings, I have a workbook with 50+ worksheets in it. Each day when I
launch my workbook I have to scroll down to the last few visible rows to be
able to enter in the new data for the day. Every time, 50 times... It's
getting a little tedious. Is there a way to code the worksheet/workbook so
that the worksheet opens up so that the last two or three filled rows are
visible at the top of the worksheet?

(I keep thinking, "If it default opens to the first row, surely there is a
way to get it to default open to the "x" row...")
Thanks,