CODEWITHSUNDEEP
excelvba
Michael T
Michael T

Reputation: 1955

Excel VBA code to move a sheet to the right or left by one

I have a workbook with any number of worksheet tabs. I want to make command buttons to "Move Left" and "Move Right" which would move the worksheet within the workbook to the left or right (ie, to the left of worksheet to its left or to the right of the worksheet to it's right). Is there an easy way to do this? It seems with ActiveSheet.Move you have to know the name of the sheet to move it before or after. I don't know how to get the name of the sheet to its immediate right or left.

Upvotes: 1

Views: 2810

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

This will move the active sheet right:

Sub moveright()
    Dim s As Worksheet
    Set s = ActiveSheet
    If s.Next Is Nothing Then Exit Sub
    s.Move after:=s.Next
End Sub

This will move the active sheet left:

Sub moverleft()
    Dim s As Worksheet
    Set s = ActiveSheet
    If s.Previous Is Nothing Then Exit Sub
    s.Move before:=s.Previous
End Sub

Upvotes: 4

Related Questions