Organizing new sheets as they are created based on a column range

[sevink]

I am very new to excel and VBA in general. I am using the following code in a userform to generate a new sheet based on a column range named "Weeks", each cell is formatted as 07-JAN, 14-JAN, 21-JAN etc. I use this column to provide a list of weeks for data collection and to provide a predefined list of weeks to create new sheets. I would like the sheets to organize themselves no matter when they may created. so if I have a sheet for week 1,2,3,5,8 and I want to create Week 7 then it will place it between 5 and 8. I assume the process is to assign the new sheet name to a var then look at each cell before it and if it exists, then put that value into a variable which would be the entry for .copy After function. I struggle with how to do this efficiently though. obviously, the column range is 52 cells long if that helps at all. Thank you for any help.

Private Sub CommandButton1_Click()
Dim exists As Boolean

For I = 1 To Worksheets.Count
    If Worksheets(I).Name = ComboBox1 Then
        exists = True
        MsgBox ComboBox1 & " already exists.", vbOKOnly + vbInformation
    End If
Next I

If Not exists Then
   Sheets("TEMPLATE").Copy After:=Sheets("Template")
   ActiveSheet.Name = ComboBox1

End If

    Call Unload(NewWeeklySheet)
End Sub

 

[HighAndWilder]

I had already written a generic solution to this.

You will need to make a reference to the 'Microsoft Scripting Runtime' library.
See here how to do this.

Use this line to call the code
subSortWorksheets(ActiveWorkbook, xlAscending)

This line will order the sheets in descending order.
Call subSortWorksheets(ActiveWorkbook, xldescending)

Public Sub subSortWorksheets(Wb As Workbook, Optional sortorder As XlSortOrder = xlAscending)
Dim dictWorksheets As New Scripting.Dictionary
Dim varKey As Variant
Dim Ws As Worksheet
    
    For Each Ws In Wb.Sheets
        dictWorksheets.Add key:=Ws.name, Item:=dictWorksheets.Count + 1
    Next Ws
                 
    Set dictWorksheets = fncSortDictionaryByKey(dictWorksheets, sortorder)
   
    For Each varKey In dictWorksheets
        Wb.Worksheets(varKey).Move Before:=Wb.Worksheets(1)
    Next varKey
    
    Wb.Sheets(1).Activate

End Sub

Public Function fncSortDictionaryByKey(dict As Object _
                  , Optional sortorder As XlSortOrder = xlAscending) As Object
    
    Dim arrList As Object
    Set arrList = CreateObject("System.Collections.ArrayList")
    
    ' Put keys in an ArrayList.
    Dim key As Variant, coll As New Collection
    For Each key In dict
        arrList.Add key
    Next key
    
    ' Sort the keys.
    arrList.Sort
    
    ' For descending order, reverse.
    If sortorder = xldescending Then
        arrList.Reverse
    End If
    
    ' Create new dictionary.
    Dim dictNew As Object
    Set dictNew = CreateObject("Scripting.Dictionary")
    
    ' Read through the sorted keys and add to new dictionary.
    For Each key In arrList
        dictNew.Add key, dict(key)
    Next key
    
    ' Clean up
    Set arrList = Nothing
    Set dict = Nothing
    
    ' Return the new dictionary.
    Set fncSortDictionaryByKey = dictNew
        
End Function

 

[sevink]

Thank you for the reply, it is a lot to digest as a newer programmer. From my minimal knowledge, this appears to just be a function that I would call to organize all of the sheets at the same time. Would this function along side my current method of holding the sheet names in a cell column range (named Weeks)? if so where in the code would this be set? Also, I assume that any static sheets would have to be included in their appropriate location. If this is not the case and I need to store them in a new location, where is that?

Thank you for the very detailed example.

 

[HighAndWilder]

If you want to sort ALL of the worksheets in either ascending or descending order then there is no need to keep a list of them.

Copy and paste my code into a Standard Code Modules and call it by using the following line:

subSortWorksheets(ActiveWorkbook, xlAscending)

If you only want to sort certain worksheets that are in the Weeks column then use the code below.

You will need to change reference to the worksheet and column as I have assumed that Weeks is in column 'A' in the 'Master' worksheet.

Sheets that are not in your list will still exist in the same workbook but will start after the last worksheet in your list.
They will be in the same order as before.

Public Sub subRearrangeWorksheetsBasedUponList()
Dim rng As Range

    For Each rng In Worksheets("Master").Range("A2", Worksheets("Master").Range("A" & Rows.Count).End(3))
        
        Worksheets(rng.Value).Move Before:=ActiveWorkbook.Sheets(rng.Row - 1)
  
    Next rng

End Sub