VBA Sheets Order

[hobbiton73]

Hi, I wonder whether someone may be able to help me please.

I'm using the code below to add multiple sheets to a workbook:

Sub NewSheets()
    
    Dim shArray() As Variant             'Declare the sheet Name array and a
    Dim i As Long                        'counter variable
    
        shArray = Array("BAS Consultants", _
            "Monthly Direct", _
            "Monthly Enhancements", _
            "Monthly Indirect", _
            "Monthly Overheads", _
            "Monthly Projects", _
            "Yearly Direct", _
            "Yearly Enhancements", _
            "Yearly Indirect", _
            "Yearly Overheads", _
            "Yearly Projects", _
            "C&R In Flight Projects", _
            "S&A In Flight Projects", _
            "C&R Graph Data", _
            "S&A Graph Data", _
            "C&R Flexible Resource Profile", _
            "S&S Flexible Resource Profile", _
            "C&R Flexible Resource KPI", _
            "C&R Resource Capacity")    'Populate the array
            
            For i = LBound(shArray) To UBound(shArray)  'Loop through the elements
                Sheets.Add(After:=Worksheets(1)).Name = shArray(i)
                Next i
End Sub

The code works, but the issue I have is that the position of the sheets when the workbook has been built, doesn't reflect the order by which I've listed them in the code i.e. the "BAS Consultants" sheet becomes the last sheet in the workbook rather than the first.

Could someone possibly tell me please, is there a way to force the sheets to be created in the position by which they are set in the list above.

Many thanks and kind regards

Chris

[patel45]

            For i = LBound(shArray) To UBound(shArray)  'Loop through the elements
                Sheets.Add(After:=Worksheets(Worksheets.count)).Name = shArray(i)
            Next i

[hobbiton73]

Hi @patel45, thank you for taking the time to reply to my post and for the help.

The solution works great.

Many thanks and kind regards

Chris

[jolivanes]

Put all the sheet names to be added in Column A (starting at Cell A1).

Public Function WorkSheetExists(ByVal strName As String) As Boolean
   On Error Resume Next
   WorkSheetExists = Not Worksheets(strName) Is Nothing
End Function

Sub Try()
Application.ScreenUpdating = False
Dim c As Range
For Each c In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
    If Not WorkSheetExists(c.Value) Then
        Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Name = c.Value
    End If
Next c
Sheets("Sheet1").Select
Application.ScreenUpdating = True
End Sub

[hobbiton73]

Hi @jolivanes, thank you very much for taking the time to reply to my post and for the suggestion, it is much appreciated.

Kind Regards

Chris