Monday, April 9, 2018

VBA code to change worksheet sequence in excel workbook!!!

Sequence of worksheet in excel workbook does matters in many times. Sometimes, it requires lot of effort to change order of sheets. Obviously, it might be one of tedious job when it comes to huge number of worksheet exists in a workbook. As a solution this task can be automate using excel VBA automation. Using few line of codes this problem can be solved.

In this topic, we will see how to change sequence of worksheets using excel macro.

Make sure that required sequence of sheets are listed in Sheet1 starting from A1 cell. Once, you list down sequence of sheets in A column in Sheet1 macro will be ready to execute.

Now, we are ready to execute below code to change worksheet sequence in excel workbook.

Option Explicit

Sub Change_Worksheet_Sequence()
' Author: Dreams24
' Written for VBA Tricks and tips blog

' Declare variables
Dim lRow As Integer
Dim i As Integer

'Counts sheet names stored under Sheet1 tab in A column starting for range A1
'You can change "Sheet1" tab and A column as you need
lRow = ThisWorkbook.Sheets("Sheet1").Range("A1").End(xlDown).Row

'For loop to change worksheet sequence
For i = 1 To lRow
    'You can change "Sheet1" tab and A column as you need
    Sheets(ThisWorkbook.Sheets("Sheet1").Range("A" & i).Value).Move after:=Sheets(lRow)
Next i

MsgBox "You have successfully changed the worksheet sequence.", vbInformation

End Sub

No comments: