Navigate around cells, worksheets, and files by using Visual Basic Codes

movement logo

File to download:  Movement by using Visual Basic Codes.xlsm

Requirement:  Visual Basic knowledge

Related article: Different Looping Mechanisms in Excel VBA

msofficeshare logo

When we automate the Excel worksheets by using Visual Basic (VB) codes, we frequently have to make the cursor moved to pre-determined locations to do calculations, copy, cut, paste, sort, and filter data. If you know how to combine the movements with the looping, you have an ultimate tool for automation.  The payoff is tremendous and well worth the time to acquire such skill.

You can start with recording a macro (VB module) by using the “Relative References” command.  Here are the steps:

  • Click the “View” tab on the ribbon
  • Click the triangle under the “Macros” and choose the “Use Relative References” command.
  • Then click the “Record Macro…” to start recording your VB module.
pic1 movement

When you select the “Use Relative References” command, the VB codes will be recorded as a relative reference instead of absolute reference to the cell that is activated.  For example, if you want to copy the data from cell A1 to C1, the VB codes will be recorded as cell C1 (Range(“C1”)) as the paste location if you do not choose the “Use Relative References” command.  If you choose the “Use Relative References” command, the VB codes will be recorded as ActiveCell.Offset(0, 2) instead.  The difference is that if you use the absolute reference, your data will always be pasted to cell C1, no matter which cell your copy data is from.  However, when you use relative reference, the paste cell location depends on the copy cell location.  For example, if my copy cell location is now A4, the paste cell location will now be C4.  The paste cell location will always be 2 cells on the right of the copy cell location.  The use of “Use Relative References” command will give your macro far more flexibility than using the absolute reference.  However, if you know the VB codes, it may be a lot faster just to type them in than recording them.

The OFFSET function is the tool for basic cursor movements in VB codes.  The syntax is OFFSET(row reference, column reference). Please see the below examples.

  • Move cursor to one cell right

 

Sub MoveRight()

    ActiveCell.Offset(0, 1).Select

End Sub

 

  • Move cursor to one cell left

 

Sub MoveLeft()

    ActiveCell.Offset(0, -1).Select

End Sub

 

  • Move cursor to one cell up

 

Sub MoveUp()

    ActiveCell.Offset(-1, 0).Select

End Sub

 

  • Move cursor to one cell down

 

Sub MoveDown()

    ActiveCell.Offset(1, 0).Select

End Sub

 

  • Move cursor diagonally to one cell right and one cell down

 

Sub MoveDiagonal()

    ActiveCell.Offset(1, 1).Select

End Sub

 

  • Move cursor diagonally to one cell left and one cell up

 

Sub MoveDiagonal2()

    ActiveCell.Offset(-1, -1).Select

End Sub

Change the number of the row or column reference to move the cursor farther away.  For example, ActiveCell.Offset(0, 5) will move the cursor 5 cells right of the current active cell.

 

Another set of cursor movements involve the use of the END function in VB codes.  The use of the END function is the same as using the END key on your keyboard.  The logics of movement are as below.  For illustration purpose, I use the End(xlToRight) function, the xlToLeft, xlUp, and xlBottom share the same logics of movement as xlToRight.  

  • If the row is completely empty, the Visual Basic Codes will move the cursor to the end of the row.
  • If the row has data and you place the cursor on the cell with data, the Visual Basic Codes will move to the last cell with data on the right end of the row.
  • If the row has data and you place the cursor on an empty cell, the Visual Basic Codes will move to the right to search for the first cell with data.
  • If the row has data and you place the cursor on the last cell with data, the Visual Basic Codes will move to the right to search for the first cell with data of another data segment.  If there are no more data on the right, the Visual Basic Codes will move the cursor to the end of the row.

Below are the codes will move the cursor to right, left, top, and bottom.

Sub MoveRightEnd()

    ActiveCell.End(xlToRight).Select

End Sub

 

Sub MoveLeftEnd()

    ActiveCell.End(xlToLeft).Select

End Sub

 

Sub MoveToTop()

    ActiveCell.End(xlUp).Select

End Sub

 

Sub MoveToBottom()

    ActiveCell.End(xlDown).Select

End Sub

If you have multiple worksheets in a file (workbook), you may need to jump from one worksheet to another.  Below are the codes that do the job.

Sub MoveToNextWorksheet()

    ActiveSheet.Next.Select

End Sub

 

Sub MoveToPreviousWorksheet()

    ActiveSheet.Previous.Select

End Sub

 

Sub MoveToFirstWorksheet()

    Worksheets(1).Select

End Sub

 

Sub MoveToLastWorksheet()

    Worksheets(Worksheets.Count).Select

End Sub

 

Sub MoveToSpecifiedWorksheet()

    WorksheetNameSelected = Range("WorksheetName").Value

    Sheets(WorksheetNameSelected).Select

End Sub

If you want to select a range of cell for copy, cut, and paste, you can use the below VB codes to do the trick.  The key is to identify the beginning and ending cell of the range and then select them.

Sub SelectRange()

    ActiveCell.Select

    BeginCell = ActiveCell.Address

    ActiveCell.End(xlDown).Select

    EndCell = ActiveCell.Address

    Range(BeginCell, EndCell).Select

End Sub

If you want to select an entire worksheet, you can use this VB codes.  I usually use this VB codes to select and then erase all the previous data before pasting new ones on the worksheet.

Sub SelectEntire()

    Cells.Select

End Sub

If you need to copy data from one file (workbook) and paste to another file, you can use the below VB codes.

Sub BetweenFiles()

 

    ThisWorksheetName = Range("ThisWorksheet").Value

    AnotherWorksheetName = Range("AnotherWorksheet").Value

 

    Windows(AnotherWorksheetName).Activate

    'you can do calculation, copy and cut data

 

    Windows(ThisWorksheetName).Activate

    'you can paste data here, do sorting, filtering, etc.

End Sub

You can download this file (Movement by using Visual Basic Codes.xlsm) which has the examples and VB codes that listed in this article.