Maintaining row position when switching sheets

[jimpappas]

Hello. I'm new to visual basic and have a question I hope somebody can answer. I have an excel workbook with 2 sheets. There are a ton of rows in each sheet. I wish to be able to go from one sheet to another and automatically maintain row position. For example, if I am in sheet A, row 100 and go to sheet B, it will start me in the A column of row 100. Any help is much appreciated! Jim

 

[JLGWhiz]

It is all in how the code is written. The main thing to remember is that the computer is dumb as a rock. It is super fast, but it has to be told what to do. It also has a great memomory, so once it is told something it won't forget until something causes that memory to be erased or replaced with a new value. So, one way to have that same row (eg, row = 10) value when changing sheets is to give the row a variable value:

Dim myRow As Long
myRow = 10
Sheets(1).Rows(myRow).Copy
Sheets(2).Cells(myRow, 1).PasteSpecial xlPasteValues
Application.CutCopyMode = False

There are other examples, but basically, the variable is the means to control row number values between sheets. You can also find rows by relative proximity to something in a known location from one sheet to another. In the simplest form, the code writer translates to code the same logic a human uses to do it manually.

I suggest you browse some of the postings on this site to pick up some of the techniques used by different responders.

 

[Joe4]

Well, you could create a little macro to do what you want. So if you use the Macro, which you could attach to a button, icon, keyboard shortcut, etc, you could just use that to move to the same address on the other sheet. That code will look something like this:

Sub MySheetMove()

    Dim mySheet As String
    Dim myAddress As String
    
'   Capture sheet index
    mySheet = ActiveSheet.Index

'   Capture current address
    myAddress = ActiveCell.Address
    
'   Go to other sheet
    If mySheet = 1 Then
        Sheets(2).Activate
    Else
        Sheets(1).Activate
    End If

'   Go to address
    Range(myAddress).Select
        
End Sub

 

[Rick Rothstein]

Try this...

1) Press ALT+F11 to go into the VB editor

2) Find the "ThisWorkbook" item in the list inside the "Project-VBAProject" window and double click it.

3) Copy/paste the following code into the code window that just opened up...

Public CurrentlySelectedRow As Long

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
  If CurrentlySelectedRow = 0 Then CurrentlySelectedRow = ActiveCell.Row
  Application.Goto Rows(CurrentlySelectedRow), True
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
  CurrentlySelectedRow = Target(1).Row
End Sub

4) That's it... each page you visit will automatically start with the last active row from the sheet you just left at the top of the worksheet.

5) If you are using XL2007 or above and want this functionality to remain with the workbook the next time you open it, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[Joe4]

Pretty slick, Rick!
I was wondering if you could use events to do it, but wasn't quite sure how to pull it off. I haven't used those particular Workbook events very much.

 

[Rick Rothstein]

Pretty slick, Rick!

Thanks Joe!

I was wondering if you could use events to do it, but wasn't quite sure how to pull it off.

The key is the public variable so that conditions that exist during one event can be used in the next event. I made extensive use of this technique back in my volunteering days for the compiled version of Visual Basic (in the early to mid-2000s).

 

[jimpappas]

Rick
I really appreciate that. It is super slick. I would like to follow-up on one more thing as this has one glitch (that is my fault, as I oversimplified things slightly). I actually have 5 worksheets (call them A,B,C,D,E) with the first 3 essentially being instructions. That said, I would like worksheet D and worksheet E to maintain row position but not worksheets A-C, as this would take the user way below the instructions. I would imagine that this can be incorporated somehow into the IF statement, and can definitely investigate that. However, if you have some simple guidance or helpful hints on that I would again appreciate the assistance. This is an incredible forum! Jim