4

I want to rearrange the sheets in my workbook. I have many sheets and moving the last one to the front is very slow with drag and drop. Ideally I want a command that will select a sheet and move it to the first position. Is this possible?

OR, the sheet names are "month year" perhaps they can be sorted?

  • Ctrl+/ will show you all of the available keyboard shortcuts. – ale Nov 1 '14 at 18:06
2

This script will allow you to move the active sheet to the beginning or a pick sheet that needs to be moved.

Code

// global
var app = SpreadsheetApp.getUi();
var ss = SpreadsheetApp.getActiveSpreadsheet();

// create menu entry in spreadsheet upon opening
function onOpen() {
  app.createMenu('Move Sheet')
    .addItem('Move Active', 'moveactiveSheet')
    .addItem('Move Sheet', 'movespecificSheet')
    .addToUi();  
}

// move active sheet to position zero
function moveactiveSheet() {
  ss.moveActiveSheet(0);  
}

// select sheet number (correct for zero-based array) and move to front
function movespecificSheet() {
  var int = app.prompt('Select Sheet', 'Use int. only', app.ButtonSet.OK_CANCEL)
    .getResponseText();
  ss.setActiveSheet(ss.getSheets()[int-1]);
  ss.moveActiveSheet(0);  
}

Example

I've created an example file for you: move sheets.
Make a copy of my file or add the code under Tools > Script editor. Press the bug button to authenticate the script.

  • 1
    This made my task much easier. Not a perfect solution, but workable. And I learned some scripting. – SuperJames Nov 1 '14 at 23:19
3

Unfortunately, it looks like drag-and-drop is going to be your most efficient method.

While Alt+Shift+S will open the "Sheet menu", you'll still need to use the arrow keys and Enter (or mouse click) to choose "Move right"/"Move left". Even so, you'll only move the sheet one space in either direction. There's no "Move to front" option.

There's also no way within the normal Spreadsheets interface to sort the individual sheet tabs. (That's not to say someone couldn't put together a script to do it.)

0

Jacob Jan Tuinstra's script was great! And a frustration- and time-saver! (Talk about frustration when you are dragging a tab and you're almost to the spot, and then you drop it (or lose it) in a place that isn't quite right and it jumps back to where you started!)

I've added some additional script tabs to the project to work with my document which has around 85+ sheets in it. I have revised the line:

ss.moveActiveSheet(0)

to: ss.moveActiveSheet(55) to move the active sheet to the middle of my document (which is at the end of a group of specific sheets),

or: ss.moveActiveSheet(85) to move the active sheet to the end (versus the beginning) of the document.

I found if you set the end sheet number too high, the script will fail when you run the debug, so if you don't want to count how many sheets you have, just play with the number.

Thanks, Jacob!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.