Worksheet(s) open to last used row

[PapaBear]

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,

 

[Mike H]

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]

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

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

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,

 

[Mike H]

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

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

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

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,

 

[PapaBear]

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

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

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

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

:

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,

 

[Mike H]

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

Mike

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

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

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

:

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

:

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,