Excel Annoyances

NAVIGATION AND DISPLAY ANNOYANCES

KEEP THE SAME ACTIVE CELL WHEN YOU MOVE TO A NEW WORKSHEET

The Annoyance:

I switch around a lot between fairly similar worksheets, and it would save me hours of hassle if I could move from one worksheet to another and keep my cursor in the same position in the new sheet as the one I just left.

The Fix:

Excel is designed to remember the last active cell on each worksheet, and to reactivate that cell when you open that sheet again. However, here’s a macro you can use that will move you to the next worksheet in your workbook and place your cursor in the same active cell (it works in every version of Excel since Excel 97):

   Sub NextSheetSameCell()

   Dim rngCurrentCell As Range
   Dim shtMySheet As Worksheet
   Dim strCellAddress As String

   'strCellAddress = ActiveCell.Address

   'Comment out the line above to record the
   'selected range's address, or comment out the
   'line below to record the active cell's address.

   strCellAddress = _
    ActiveWindow.RangeSelection.Address

   Set shtMySheet = ActiveWindow.ActiveSheet

   If Worksheets.Count > shtMySheet.Index Then

    shtMySheet.Next.Activate
    Range(strCellAddress).Activate

    Else

    Worksheets(1).Activate
    Range(strCellAddress).Activate

   End If

   Set shtMySheet = Nothing

   End Sub

For good measure, I wrote the macro so that you can move to either the same active cell or the same range of selected cells as you move from worksheet to worksheet. In either case, the macro records the address of the selected cell or cells, activates the next worksheet ...