Moving sheets within a workbook is one of those things that’s just easier with a mouse. You can click on a sheet, drag it, and the little arrow tells you where it will land. If you hold down the Ctrl key while you do it, you’re copying.
If you’re more of a keyboard guy (ahem), you probably already know that you can use Alt+HOM (Home > Format > Move or Copy Sheet…) to get the Move or Copy dialog. Of course it’s under Format. I mean, really, that’s the natural place for it. Home > Format is the Insert Tab of dropdowns, if you get my meaning.
That dialog defaults to the same workbook, so you’re moving within the workbook by default. Back in 2009, I took over the Move or Copy dialog as I am wont to do. That uses Alt+EV – the 2003 method for displaying the Move or Copy dialog. Back then, I settled on using my mouse to move sheets within a workbook and using my simpler dialog for moving them between workbooks. I’m very happy with my dialog box and use it all the time.
Today I decided to make a keyboard shortcut for moving within a workbook. I already have code to wrap while switching worksheets. I decided on Ctrl+Alt+PgUp/PgDn. Just Ctrl is how you navigate from one worksheet to the next. Ctrl+Shift groups worksheets together. So that left me with Ctrl+Alt. After one use, I can honestly say that I’m in love.
First I add my shortcuts to Auto_Open
|
1 2 |
Application.OnKey "^%{PGUP}", "MoveSheetsUp" Application.OnKey "^%{PGDN}", "MoveSheetsDown" |
and to turn them off in Auto_Close
|
1 2 |
Application.OnKey "^%{PGUP}" Application.OnKey "^%{PGDN}" |
The code is pretty darn similar to the Wrap Sheets code except that it moves the sheet rather than activating it.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
Sub MoveSheetsUp() If ActiveSheet.Index = FirstVisibleSheetIndex Then If Timer - msnLastWrap > msnWRAPBUFFER Then gclsAppEvents.AddLog "^+{PGUP}", "MoveSheetsUp" ActiveSheet.Move , ActiveWorkbook.Sheets(LastVisibleSheetIndex) End If Else ActiveSheet.Move ActiveWorkbook.Sheets(NextVisibleSheetIndex(False)) End If msnLastWrap = Timer End Sub Sub MoveSheetsDown() If ActiveSheet.Index = LastVisibleSheetIndex Then If Timer - msnLastWrap > msnWRAPBUFFER Then gclsAppEvents.AddLog "^+{PGDN}", "MoveSheetsDown" ActiveSheet.Move ActiveWorkbook.Sheets(FirstVisibleSheetIndex) End If Else ActiveSheet.Move , ActiveWorkbook.Sheets(NextVisibleSheetIndex(True)) End If msnLastWrap = Timer End Sub |
If you don’t want to add this code to your PMW (YET!), you can try it out here.
You can download MoveSheetsKeyboard.zip
Oh, that’s awesome.
The move/copy sheet shortcut for 2003 is alt+e, m and still works.
I though Alt+e, v was the shortcut and I stole it. But you’re right, it’s “m” and I just made my own. How quickly I forget.
Why not simply: