File to download: Movement by using Visual Basic Codes.xlsm
Requirement: Visual Basic knowledge
Related article: Different Looping Mechanisms in Excel VBA
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:
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.
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.
Below are the codes will move the cursor to right, left, top, and bottom.
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.
WorksheetNameSelected = Range("WorksheetName").Value
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.
BeginCell = ActiveCell.Address
EndCell = ActiveCell.Address
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.
If you need to copy data from one file (workbook) and paste to another file, you can use the below VB codes.
ThisWorksheetName = Range("ThisWorksheet").Value
AnotherWorksheetName = Range("AnotherWorksheet").Value
'you can do calculation, copy and cut data
'you can paste data here, do sorting, filtering, etc.
You can download this file (Movement by using Visual Basic Codes.xlsm) which has the examples and VB codes that listed in this article.