Moving Sheet Groups within a Workbook

Last week I created a keyboard shortcut to move a sheet within a workbook. This week I’m changing it to work with groups of sheets rather than just the active sheet. Not because I need it. I rarely work with grouped sheets as it is. But sometimes you have to program just for the fun of it.

This will require a change to NextVisibleSheetIndex function. I tried to determine if the ActiveSheet was in a group and where it was in the group. That resulted in some inelegant code and I could tell I was doing it wrong. Then I realized that I should stop futzing with the ActiveSheet and just pass a sheet into the function where I want to start. That made things much simpler.

That kept the function code cleaner, but I still had to figure out what sheet to pass in. Well, that turned out to be really easy. If It was moving left, I pass in the first sheet in the group.

And if I’m moving right, I pass in the last sheet.

3 thoughts on “Moving Sheet Groups within a Workbook

  1. How did you avoid the selected sheets coming ungrouped when trying to move them multiple times? I tried implementing your code in Excel 2013, and the first sheet in the group always becomes the only active/selected sheet after they are moved, requiring that they be reselected as a group in order to be moved a second time. Thus my personal solution was to conclude with ssh.Select so that the sheets are reselected automatically.

  2. I didn’t, apparently. I get the same behavior in 2010. It happens when you manually move grouped sheets in the UI tool. But with the mouse, you’d probably never want to move them twice.

    I think ssh.Select is probably the best answer.


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.