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.

Public Function NextVisibleSheetIndex(ByRef shStart As Object, ByVal bDown As Boolean) As Long
   
    Dim lReturn As Long
    Dim i As Long
   
    If bDown Then
        For i = shStart.Index + 1 To ActiveWorkbook.Sheets.Count
            If ActiveWorkbook.Sheets(i).Visible Then
                lReturn = i
                Exit For
            End If
        Next i
    Else
        For i = shStart.Index - 1 To 1 Step -1
            If ActiveWorkbook.Sheets(i).Visible Then
                lReturn = i
                Exit For
            End If
        Next i
    End If
   
    NextVisibleSheetIndex = lReturn
   
End Function

Public Function NextVisibleSheetIndex(ByRef shStart As Object, ByVal bDown As Boolean) As Long

Dim lReturn As Long

Dim i As Long

If bDown Then

For i = shStart.Index + 1 To ActiveWorkbook.Sheets.Count

If ActiveWorkbook.Sheets(i).Visible Then

lReturn = i

Exit For

End If

Next i

Else

For i = shStart.Index - 1 To 1 Step -1

If ActiveWorkbook.Sheets(i).Visible Then

lReturn = i

Exit For

End If

Next i

End If

NextVisibleSheetIndex = lReturn

End Function

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.

Sub MoveSheetsUp()
    
    Dim ssh As Sheets
    
    Set ssh = ActiveWindow.SelectedSheets
    
    If ssh(1).Index = FirstVisibleSheetIndex Then
        If Timer - msnLastWrap > msnWRAPBUFFER Then
            gclsAppEvents.AddLog "^%{PGUP}", "MoveSheetsUp"
            ssh.Move , ActiveWorkbook.Sheets(LastVisibleSheetIndex)
        End If
    Else
        ssh.Move ActiveWorkbook.Sheets(NextVisibleSheetIndex(ssh(1), False))
    End If
    
    msnLastWrap = Timer
    
End Sub

Sub MoveSheetsUp()

Dim ssh As Sheets

Set ssh = ActiveWindow.SelectedSheets

If ssh(1).Index = FirstVisibleSheetIndex Then

If Timer - msnLastWrap > msnWRAPBUFFER Then

gclsAppEvents.AddLog "^%{PGUP}", "MoveSheetsUp"

ssh.Move , ActiveWorkbook.Sheets(LastVisibleSheetIndex)

End If

Else

ssh.Move ActiveWorkbook.Sheets(NextVisibleSheetIndex(ssh(1), False))

End If

msnLastWrap = Timer

End Sub

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

Sub MoveSheetsDown()
    
    Dim ssh As Sheets
    
    Set ssh = ActiveWindow.SelectedSheets
    
    If ssh(ssh.Count).Index = LastVisibleSheetIndex Then
        If Timer - msnLastWrap > msnWRAPBUFFER Then
            gclsAppEvents.AddLog "^%{PGDN}", "MoveSheetsDown"
            ssh.Move ActiveWorkbook.Sheets(FirstVisibleSheetIndex)
        End If
    Else
        ssh.Move , ActiveWorkbook.Sheets(NextVisibleSheetIndex(ssh(ssh.Count), True))
    End If
    
    msnLastWrap = Timer
    
End Sub

Sub MoveSheetsDown()

Dim ssh As Sheets

Set ssh = ActiveWindow.SelectedSheets

If ssh(ssh.Count).Index = LastVisibleSheetIndex Then

If Timer - msnLastWrap > msnWRAPBUFFER Then

gclsAppEvents.AddLog "^%{PGDN}", "MoveSheetsDown"

ssh.Move ActiveWorkbook.Sheets(FirstVisibleSheetIndex)

End If

Else

ssh.Move , ActiveWorkbook.Sheets(NextVisibleSheetIndex(ssh(ssh.Count), True))

End If

msnLastWrap = Timer

End Sub

3 thoughts on “Moving Sheet Groups within a Workbook”

Atlas says:

September 7, 2016 at 4:55 am

Thank you for share.
David N says:

January 25, 2017 at 9:59 am

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.
Dick Kusleika says:

January 25, 2017 at 2:15 pm

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.