|
|
|
|
|
|
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()
On Error Resume Next
Application.Goto Reference:=ActiveWorkbook.Sheets(ActiveSheet.Index - 1).Range(ActiveCell.Address)
If Err.Number = 0 Then Exit Sub
MsgBox ("Action not possible, you are on the first sheet")
End Sub
Sub up_one_sheet_same_cell()
On Error Resume Next
Application.Goto Reference:=ActiveWorkbook.Sheets(ActiveSheet.Index + 1).Range(ActiveCell.Address)
If Err.Number = 0 Then Exit Sub
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 154 times.
|
|