How to Make Excel Move Automatically to the Next Cell (4 Suitable Ways)

Looking for ways to know how to make Excel move automatically to the next cell? Sometimes, we want our Excel to move to the next cell automatically to make our work easier. Here, you will find 4 different step-by-step explained ways to make Excel move automatically to the next cell.


Download Practice Workbook


4 Ways to Make Excel Move Automatically to Next Cell

We can make Excel move automatically to the next cell by using the following 4 step-by-steps ways.


1. Using VBA to Make Excel Move Automatically to Next Cell

Here, we have a dataset containing the values of User Name of some students on a website. In the User Name, the first 5 letters are their Name and the rest of the 5 letters are their Roll numbers.

Now, we will use VBA to automatically move the letters to the next cells.

Using VBA to Make Excel Move Automatically to Next Cell

Follow the steps below to do it on your own.

Steps:

  • First, go to the Developer tab >> click on Visual Basic.

  • Now, the Microsoft Visual Basic for Applications will open.
  • Then, go to the Insert tab >> select Module.

Using VBA to Make Excel Move Automatically to Next Cell

  • Then, write the following code in your Module.
Sub Move_to_Next_Cell()
Dim Str As String
Dim start_position As Integer
Dim column_number As Integer
Str = Application.InputBox("Enter value", "Move to Next Cell", , , , , , 2)
column_number = 0
Application.ScreenUpdating = False
For start_position = 1 To Len(Str) Step 5
    ActiveCell.Offset(0, column_number) = "'" & Mid(Str, start_position, 5)
    column_number = column_number + 1
Next
Application.ScreenUpdating = True
End Sub

Code Breakdown

  • First, we created a Sub Procedure as Move_to_Next_Cell.
  • Then, we declared Str as String, start_position and column_number as Integer.
  • After that, we set InputBox named as Move to Next Cell to Enter Value.
  • Next, we selected column_number=0
  • Afterward, we set the Screen Updating to False.
  • Then, we used a For loop from start_position = 1 to the length of the Str and set Step as 5.
  • Now, we used the VBA Mid function to extract the first 5 character and by using the VBA Offset function moved the extracted values to the next cell.
  • After that, incremented the column_Number by using column_Number = column_Number+ 1.
  • Next, we set the Screen Updating as True.
  • Next, click on the Save button and go back to your worksheet.

Using VBA to Make Excel Move Automatically to Next Cell

  • Now, select the Cell C5.
  • After that, go to the Developer tab >> click on Macros.

  • Now, the Macros box will appear.
  • Then, select Five_Char_Entry.
  • After that, click on Run.

Using VBA to Make Excel Move Automatically to Next Cell

  • Next, the Move to Next Cell box will open.
  • Then, in the Enter value box select Cell B5.
  • After that, click on OK.

  • Now, you can see that the Cell value of B5 has moved to Cell C5 and Cell D5. Cells C5 contains the first 5 letters and Cell D5 contains the rest of the 5 letters.

Using VBA to Make Excel Move Automatically to Next Cell

  • Finally, follow the same steps we have done for Cell D5, for the rest of the cells to move Excel automatically to the next cell.


2. Use of Advanced Feature from Excel Options to Move Automatically to Next Cell

Sometimes when we click on the ENTER Excel does not move to the next Cell. Here, we will use Advanced Excel Options to move automatically to the next cell in Excel.

Use of Advanced Feature from Excel Options to Move Automatically to Next Cell

Steps:

  • First, select click on Cell B5.
  • Then, go to the File tab.

  • After that, click on Options.

Use of Advanced Feature from Excel Options to Move Automatically to Next Cell

  • Now, the Excel Options box will open.
  • Next, go to the Advanced option.
  • Then, turn on the After pressing Enter, move selection and set Direction as Down.
  • After that, press OK.

  •  Finally, click ENTER.
  • Now, you can see that Cell B5 has moved downward to Cell B6.

Use of Advanced Feature from Excel Options to Move Automatically to Next Cell


3. Applying Protect Sheet Feature to Move Automatically to Next Cell

Now, we will apply Protect Sheet Feature to move Excel automatically to the next cell.

Applying Protect Sheet Feature to Move Automatically to Next Cell

Go through the steps given below to do it on your own.

Steps:

  • First, select the Cell range B4:D10 and Right Click.

  • Then, click on Format Cells.

Applying Protect Sheet Feature to Move Automatically to Next Cell

  • Now, the Format Cells box will appear.
  • Next, go to the Protection tab.
  • After that, unselect the Locked option.
  • Then, click on OK.

  • Next, go to the Review tab >> click on Protect >> select Protect Sheet.

Applying Protect Sheet Feature to Move Automatically to Next Cell

  • Now, the Protect Sheet box will open.
  • After that, turn on the Protect worksheet and contents of locked cells.
  • Then, set a Password. Here, we set “123” as Password.
  • Next, unselect the Select locked cells option.
  • Afterward, click on OK.

  • Then, to confirm the Password type the Password again in the box.
  • Next, click on OK.

Applying Protect Sheet Feature to Move Automatically to Next Cell

  • Now, click on Cell B10.

  • Next, press ENTER.
  • Here, you can see that the Cell B10 has moved to Cell C4 rather than Cell B11 as the Cell range has been locked using the Protect Sheet Feature.

Applying Protect Sheet Feature to Move Automatically to Next Cell


4. Use of Name Box to Make Excel Move Automatically to Next Specific Cell

In the final method, we will show you how to make Excel move automatically to the next specific Cell using the Name Box.

Here, we have a dataset where we have to insert the Name, Roll, and Marks in Math, English, Physics, Chemistry, and Biology of a student in their Specific Cells.

Use of Name Box to Make Excel Move Automatically to Next Specific Cell

Steps:

  • First, select Cell F6.
  • Then, press CTRL and select Cell B9, C9, D9, E9, F9, and C6 respectively.

  • After that, click on the Name box.
  • Next, type Marks.
  • Then, press ENTER.

Use of Name Box to Make Excel Move Automatically to Next Specific Cell

  • Now, you can see that Cell C6 is selected.
  • Next, insert any Name of your own preference. Here, we will insert “Lucas”.

  • Afterward, press ENTER.
  • Then, Cell C6 will move to Cell F6.
  • Next, insert “12001” as Roll.

Use of Name Box to Make Excel Move Automatically to Next Specific Cell

  • Now, if you press ENTER the Cells will move to Cell B9, C9, D9, E9, and F9 respectively, and insert the data according to your preference.

Here, we inserted 94, 81, 77, 65, and 85 in Cell B9, C9, D9, E9, and F9 respectively.


Practice Section

In this section, we are giving you the dataset to practice on your own and learn to use these methods.

Practice Section


Conclusion

So, in this article, you will find 4 ways to make Excel move automatically to the next cell. Use any of these ways to accomplish the result in this regard. Hope you find this article helpful and informative. Feel free to comment if something seems difficult to understand. Let us know any other approaches which we might have missed here. And, visit ExcelDemy for many more articles like this. Thank you!

Arin
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo