VBA Express : Excel - Code to move to the same cell in either the next or previous worksheets

Excel

Code to move to the same cell in either the next or previous worksheets

Ease of Use

Easy

Version tested with

2003

Submitted by:

brettdj

Description:

Excel moves to the same cell in the next or previous sheets. If Excel is already on the first or last sheet then a message box flags this to the user.

Discussion:

I attached these two subs to a custom toolbar. I find them very handy for moving between sheets that use a common template layout as Excel normally takes you to the used cell activated on that sheet.

Code:

instructions for use

Option Explicit Sub down_one_sheet_same_cell() 'Look for error that will result if the user is on the first sheet 'and the previous sheet is non-existent On Error Resume Next Application.Goto Reference:=ActiveWorkbook.Sheets(ActiveSheet.Index - 1).Range(ActiveCell.Address) 'If there is no error then exit sub If Err.Number = 0 Then Exit Sub 'User was on first sheet MsgBox ("Action not possible, you are on the first sheet") End Sub Sub up_one_sheet_same_cell() 'Look for error that will result if the user is on the last sheet 'and the next sheet is non-existent On Error Resume Next Application.Goto Reference:=ActiveWorkbook.Sheets(ActiveSheet.Index + 1).Range(ActiveCell.Address) 'If there is no error then exit sub If Err.Number = 0 Then Exit Sub 'User was on last sheet MsgBox ("Action not possible, you are on the last sheet") End Sub

How to use:

Copy the code above.
Open your workbook.
Hit Alt+F11 to open the Visual Basic Editor (VBE).
From the menu, choose Insert-Module.
Paste the code into the code window at right.
Close the VBE, and save the file if desired.

Test the code:

To add the code to custom buttons:
View....Toolbars.....Customise
tab to Commands
Categories......Macros.....Custom Buttom
drag two custom buttons to the toolbar, and then assign the two macros by clicking on the custom buttons and picking a macro

Sample File:

CellMove(KB18).zip 14.31KB

Approved by mdmackillop

This entry has been viewed 155 times.