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.
Follow the steps below to do it on your own.
- 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.
- 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
- 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.
- 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.
- 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.
- 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.
- First, select click on Cell B5.
- Then, go to the File tab.
- After that, click on Options.
- 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.
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.
Go through the steps given below to do it on your own.
- First, select the Cell range B4:D10 and Right Click.
- Then, click on Format Cells.
- 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.
- 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.
- 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.
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.
- 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.
- 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.
- 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.
In this section, we are giving you the dataset to practice on your own and learn to use these methods.
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!