0

thank you for checking this question out.

I have a workbook, that has sheets with a user's name on it. The first Worksheet when the workbook opens is a 'Menu' sheet which as 4 command buttons (1 button for each user) and when they click on their button it will take them to their worksheet. What I would like to happen when they click their button is

  • go to their worksheet
  • set the cursor at the last empty row (scrolled to the location of the cursor).

I have searched the internet and haven't quite found any code that I could modify to fit in with what I would like to happen.

The current code I have for 'Menu' worksheet is: 

Private Sub CMB_Andrew_Click()
    Sheets("Andrew").Activate
    last_row = ThisWorkbook.Worksheets("Andrew").Cells(Rows.Count,2).End(xlUp).Row

End Sub

Private Sub CMB_Chris_Click()
Sheets("Chris").Activate
last_row = ThisWorkbook.Worksheets("Chris").Cells(Rows.Count, 2).End(xlUp).Row

End Sub

Private Sub CMB_Joy_Click()
Sheets("Joy").Activate
last_row = ThisWorkbook.Worksheets("Joy").Cells(Rows.Count, 2).End(xlUp).Row

End Sub

Private Sub CMB_Michael_Click()
Sheets("Michael").Activate
last_row = ThisWorkbook.Worksheets("Michael").Cells(Rows.Count, 2).End(xlUp).Row

End Sub

I just wanted to save the user having to scroll down (I need it to be as simple as possible for the end user LOL)

I'd appreciate any assistance you may have or suggest.

Cheers, ShyButterfly

Community
  • 1
  • 1
TheShyButterfly
  • 121
  • 10

1 Answers1

1

Interesting Read:

How to avoid using Select in Excel VBA macros

Also ensure

  1. That the sheet is visible before you activate it
  2. Cell can be selected before you select it

Is this what you want?

Private Sub CMB_Andrew_Click()
    Sheets("Andrew").Activate
    last_row = ThisWorkbook.Worksheets("Andrew").Cells(Rows.Count,2).End(xlUp).Row
    Sheets("Andrew").Range("B" & last_row).Select
End Sub
Community
  • 1
  • 1
Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250