I've got a macro that autofilters my main sheet based on data entered into dropdowns (names) and checkboxes, and then creates new worksheets based on those criteria. (We're creating separate work orders for individual contractors who are named in the dropdowns.) It was working great until I was asked to make the sheets' names reflect the names selected in the dropdown menus, rather than their numerical values, and when I tried to implement that, it stopped working. Here's what I've got:

Sub Test()

Dim c As Range
Dim i As Integer
Dim Flag As Boolean

For Each c In ActiveSheet.Range("I2:I10000")         'This is the column of linked cells, where the numerical value of each dropdown is stored.  The actual dropdowns are in column G.

    Dim dd As DropDown       'This section is what I added to try to pull the actual names from the dropdowns, rather than their numerical values
    For Each dd In ActiveSheet.DropDowns
        If dd.TopLeftCell.Column = 7 Then   'Only check dropdowns in column G
        myContractor = dd.List(dd.Value)
        End If
    Next dd


    Flag = True
    For i = 1 To Sheets().Count
        If Sheets(i).Name = "Work Order to " & myContractor Then Flag = False
    Next i
    
    If c.Value <> "" And c.Value <> Sheet1.[L2].Value And Flag Then
        Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Work Order to " & myContractor
        
        Sheet1.Shapes("Picture 5").Copy
        Sheets("Work Order to " & myContractor).[B2].PasteSpecial
        
        Sheet1.[H10:I10000].AutoFilter Field:=2, Criteria1:=c.Value
        Sheet1.[H10:I10000].AutoFilter Field:=1, Criteria1:=True
        
        Sheet1.[B11:B150].Copy
            With Sheets("Work Order to " & myContractor).[B11]
                .PasteSpecial xlPasteColumnWidths
                .PasteSpecial xlPasteValues
            End With
        Sheet1.[I10].AutoFilter
    
    ElseIf c.Value = Sheet1.[L2].Value And Flag Then
        Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Work Order to " & myContractor
        Sheet1.Shapes("Picture 5").Copy
        Sheets("Work Order to " & myContractor).[B2].PasteSpecial
        
        Sheet1.[H10:I10000].AutoFilter Field:=1, Criteria1:=True
        Sheet1.[H10:I10000].AutoFilter Field:=2, Criteria1:=c.Value
        
        Sheet1.[B11:B150].Copy
            With Sheets("Work Order to " & myContractor).[B11]
                .PasteSpecial xlPasteColumnWidths
                .PasteSpecial xlPasteValues
            End With
        Sheet1.[I10].AutoFilter
        
        Sheet1.[H10:I10000].AutoFilter Field:=1, Criteria1:=True
        Sheet1.[H10:I10000].AutoFilter Field:=2, Criteria1:="<>" & c.Value
        
        Sheet1.[B11:B150].Copy
            With Sheets("Work Order to " & myContractor).[B75]
                .PasteSpecial xlPasteColumnWidths
                .PasteSpecial xlPasteValues
            End With
        Sheet1.[I10].AutoFilter
        
        Sheet1.[H10:I10000].AutoFilter Field:=1, Criteria1:="<>" & True
        Sheet1.[H10:I10000].AutoFilter Field:=2, Criteria1:="<>" & ""
         Sheet1.[B11:B150].Copy
            With Sheets("Work Order to " & myContractor).[B150]
                .PasteSpecial xlPasteColumnWidths
                .PasteSpecial xlPasteValues
            End With
        Sheet1.[I10].AutoFilter
    
    End If
    
Next c
End Sub
Previously, I was just naming the new sheets like so:
Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Work Order to " & c.Value
Like I said, that was working great, but unfortunately the folks who tasked me with this project want the actual names on the sheets.

I sort of understand why my current code isn't working, but can't quite work out how to get it running correctly.