excelvbarangeworksheet

Insert Worksheet Alphabetically Within A Defined Range of Worksheets

I have a workbook with different sections of worksheets defined with "Divider" worksheets. One section example as such :

When a new company is entered into the 'Master' sheet, the 'Company Template' sheet is copied, inserted after the 'DIV - OVERVIEW' divider sheet and before the 'DIV - NEWS' divider sheet, and the worksheet is renamed using the entry text. The following code is used for the process :

Dim TEMPOverview As Worksheet

wsTEMPOverview.Copy After:=.Sheets("DIV - OVERVIEW")
ActiveSheet.Name = CStr(Nm.Text)

I would like is to have the new sheet placed alphabetically within a specified range of sheets. For instance : 'Company B' is placed between 'Company A' and 'Company C' within the 'DIV - OVERVIEW':'DIV - NEWS' range of sheets. I suppose the starting issue is how to define the range of sheets using the divider sheets. Then I would use a For loop to place the copied sheet alphabetically within the range like so :

Dim o
    For o = 1 To trackingWB.Sheets.Count
        If trackingWB.Sheets(o).Name >= companyName.Text Then
            Exit For
        End If
    Next o
    wsTEMPOverview.Copy before:=trackingWB.Sheets(o)
    ActiveSheet.Name = CStr(Nm.Text)

How would I go about this?

Solution

  • Something like this would work:

    Sub sheetTest()
    CreateSheetFromTemplate "CompanyB"
    CreateSheetFromTemplate "CompanyZZ"
    CreateSheetFromTemplate "CompanyH"
End Sub


Sub CreateSheetFromTemplate(companyName As String)
    Dim indxOv As Long, indxNews As Long, wb As Workbook, n As Long, pos As Long
    Dim wsTEMPOverview As Worksheet
    
    Set wb = ThisWorkbook
    indxOv = wb.Worksheets("DIV - OVERVIEW").Index 'positions of the bounding sheets
    indxNews = wb.Worksheets("DIV - NEWS").Index
    Set wsTEMPOverview = wb.Worksheets("template")
    
    For n = indxOv + 1 To indxNews - 1
        If wb.Worksheets(n).Name > companyName Then 'compare names
            pos = n  'insert before sheet n
            Exit For 'done schecking
        End If
    Next n
    If pos = 0 Then pos = indxNews 'new name comes after all existing sheets
    
    wsTEMPOverview.Copy before:=wb.Sheets(pos) 'copy and rename
    ActiveSheet.Name = companyName
    
End Sub

    Might want to add a check that the name doesn't already exist.