arranging worksheet in order

Question

I have work book with multiple sheet and now I want to copy some sheet from workbook and past it into new work book. the issue is sheets are not in order they are in random order. "February", "June", "April", "December" and so on. Now I want only "January", "February" & "March" sheets to be copy in order.

I tried below code and it's coping the data but not in oder i.e Januray sheet to come first then February and then March

Sub MoveSheets()
    Worksheets(Array("Janurary", "February", "March")).Copy

    Set wbNew = ActiveWorkbook
    With wbNew
    ActiveWorkbook.SaveAs FileName:="F:\WIN7PROFILE\Desktop\Myfolio testing\" & "\IRIS.xls"
        .Save
    End With
End Sub

I want sheet 1 to be January Sheet 2 to be February Sheet 3 to be March

Pᴇʜ Pᴇʜ · Accepted Answer · 2019-07-12T10:05:09

You can copy them one by one in a loop so the get copied into the correct order no matter what order they were before.

Option Explicit

Public Sub ExportWorksheets()  
    Dim SheetList() As Variant
    SheetList = Array("Janurary", "February", "March") 'this is the order you want
    'create a new workbook
    Dim NewWorkbook As Workbook
    Set NewWorkbook = Application.Workbooks.Add

    'copy each sheet in the right order
    Dim SheetName As Variant
    For Each SheetName In SheetList
        ThisWorkbook.Sheets(SheetName).Copy After:=NewWorkbook.Sheets(NewWorkbook.Sheets.Count)
    Next SheetName

    'delete the default sheet of the workbook
    Application.DisplayAlerts = False
    NewWorkbook.Sheets(1).Delete
    Application.DisplayAlerts = True

    'save and close
    NewWorkbook.SaveAs Filename:="F:\WIN7PROFILE\Desktop\Myfolio testing\" & "\IRIS.xls"
    NewWorkbook.Close SaveChanges:=False
End Sub

Note some versions of Excel create more than one empty worksheet if you create a new workbook. To be safe you can use the following code to delete all of the empty sheets.

'delete ALL the default sheets of the workbook
Application.DisplayAlerts = False
Dim i As Long
For i = 1 To NewWorkbook.Sheets(SheetList(0)).Index - 1
    NewWorkbook.Sheets(1).Delete
Next i
Application.DisplayAlerts = True

arranging worksheet in order

1 Answers