by Allen Wyatt
(last updated June 8, 2019)
Patrick wonders if there is a way to have the cell movement move to the right on one worksheet in a workbook and down on another worksheet in the same workbook. The only thing he can find is a global setting for all the worksheets in the workbook.
Patrick is correct; this setting is something that is handled globally in Excel. You can change the setting by following these steps:
Figure 1. The Advanced options of the Excel Options dialog box.
This changes the direction of movement for all workbooks you open in Excel. If you want the movement to vary for a single worksheet, you can accomplish this through the use of a few event handlers. Two of these are attached to the worksheet you want to be handled differently; just right-click on the worksheet's tab and select Code from the resulting Context menu. You can then paste the following into the Code window:
Private Sub Worksheet_Activate() Application.MoveAfterReturnDirection = xlToRight End Sub
Private Sub Worksheet_Deactivate() Application.MoveAfterReturnDirection = xlDown End Sub
These event handlers are triggered, respectively, when the worksheet is activated and when you deactivate the worksheet by selecting a different worksheet. Note that upon activation, the direction is set to xlToRight, which means that movement will by to the right. When the worksheet is deactivated, the direction is set to xlDown, which is assumed to be the default direction.
You will also need a third event handler; this one should be added to the ThisWorkbook module:
Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.MoveAfterReturnDirection = xlDown End Sub
This is necessary because if you close Excel while the "move right" worksheet is active, then the movement direction remains set to xlToRight. This means that the next time you start Excel, that becomes the default direction. By including the BeforeClose event handler, you ensure that the default is set as it should be.
There is only one small potential gotcha with this approach: When you first open a workbook, the Activate event is not triggered for whatever worksheet is first displayed. This means that if your "move right" worksheet is displayed for the workbook, by default, the movement won't be changed to xlToRight; it will still be set to the default, which is presumably xlDown. To force the event handler to run, you'll need to display a different worksheet in the workbook and then come back to the original worksheet. If you would rather not do this manually, you could add a fourth event handler, this one also in the ThisWorkbook module:
Private Sub Workbook_Open() Sheets("Sheet2").Select Sheets("Sheet1").Select End Sub
This macro selects the worksheet named Sheet2 and then immediately selects the one named Sheet1. You should, of course, change the worksheet names to reflect the ones appropriate for your workbook—just make sure that the last worksheet selected is the "move right" worksheet.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13640) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365.
Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!
Excel normally saves workbooks using a default file format that is peculiar to your version of the program. You can ...Discover More
Tired of the same old boring Excel look? You can add some life to your worksheets by introducing some animation. Here's ...Discover More
Excel allows you to specify where it stores various files used by the program. One location you can specify is where ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.