Runtime Error 1004 when trying to add a new variable

[Rufi]

Disclaimer: I'm a programming newbie who inherited a very scarey looking spreadsheet to maintain. My methods may horrify you.

Ok, I'm trying to add fuctionality to a workbook that is used to generate gradesheets for various training programs. Now how it works is that there are spreadsheets for each syllabus. One macro loads all the text data to a working sheet for the entire syllabus, while another moves the data relevant to a specific event to the final grade sheet.

Not knowing visual basic, I was able to get the macro to move from working data to the final sheet to work by just adding a new line of code cut and pasted from above with soem feilds and variable name changes.

My problem comes from tryig to use th same technique to move from the raw data to the working data. I've added lines and varialbles, but get a run time error whenever I try to run it.

Here is the function I'm working on:

Sub change_track()
    my_x = Sheets("Admin").Range("msel_Course").Value
    my_y = Sheets("Admin").Range("msel_flysim").Value
    what_syllabus_sheet = Sheets("Admin").Range("tbl_course").Cells(my_x, my_y + 2).Value
    what_track_range = Sheets("Admin").Range("tbl_course").Cells(my_x, my_y + 4).Value
    what_cts_range = Sheets("Admin").Range("tbl_course").Cells(my_x, my_y + 6).Value
    'read sorties into working column on admin page
    Set sl = Sheets("Admin").Range("list_sorties")
    Set tdl = Sheets("Admin").Range("list_training_day")
    Set mtl = Sheets("Admin").Range("list_mission_type")
    Set al = Sheets("Admin").Range("list_airspace")
    Set tl = Sheets("Admin").Range("list_time")
    Set macl = Sheets("Admin").Range("list_msn_aircraft")
    Set mreql = Sheets("Admin").Range("list_msn_req")
    Set ovrl = Sheets("Admin").Range("list_ovr") 'this line gives me the error

    sl.ClearContents
    tdl.ClearContents
    mtl.ClearContents
    al.ClearContents
    tl.ClearContents
    macl.ClearContents
    mreql.ClearContents
    ovrl.ClearContents
        
    on_sortie = 1
    
    'read training day into working column on admin page
    Set tdl = Sheets("Admin").Range("list_training_day")
    tdl.ClearContents
    
    'find track row
    track_dn = Sheets("Admin").Range("msel_track").Value

    'find sortie row
    
    'loop through track and grab sorties / training day/ etc.
    For Each mycell In Sheets(what_syllabus_sheet).Range(what_track_range).Rows(track_dn).Cells
        'skip first cell
        If mycell.Column > 1 Then
            If Not IsEmpty(mycell) Then
                sl.Cells(on_sortie, 1).Value = Sheets(what_syllabus_sheet).Range(what_cts_range).Cells(1, mycell.Column).Value
                tdl.Cells(on_sortie, 1).Value = Sheets(what_syllabus_sheet).Range(what_cts_range).Cells(2, mycell.Column).Value
                mtl.Cells(on_sortie, 1).Value = Sheets(what_syllabus_sheet).Range(what_cts_range).Cells(3, mycell.Column).Value
                al.Cells(on_sortie, 1).Value = Sheets(what_syllabus_sheet).Range(what_cts_range).Cells(4, mycell.Column).Value
                tl.Cells(on_sortie, 1).Value = Sheets(what_syllabus_sheet).Range(what_cts_range).Cells(5, mycell.Column).Value
                macl.Cells(on_sortie, 1).Value = Sheets(what_syllabus_sheet).Range(what_cts_range).Cells(6, mycell.Column).Value
                mreql.Cells(on_sortie, 1).Value = Sheets(what_syllabus_sheet).Range(what_cts_range).Cells(7, mycell.Column).Value
                ovrl.Cells(on_sortie, 1).Value = Sheets(what_syllabus_sheet).Range(what_cts_range).Cells(8, mycell.Column).Value 
                
                on_sortie = on_sortie + 1
            End If
        End If
    Next
    on_sortie = on_sortie - 1
    Set my_dd_box = Sheets("GradeSheet").Shapes("dd_sorties")
    Set my_used_range = Sheets("Admin").Range(Sheets("Admin").Range("list_sorties").Cells(1, 1), Sheets("Admin").Range("list_sorties").Cells(1, 1).Offset(on_sortie - 1, 1))
    
    ' Set the first mission to be selected
    Sheets("Admin").Range("msel_gs").Value = 1
    
    're-fill list of sorties
    my_dd_box.ControlFormat.ListFillRange = "'Admin'!" & my_used_range.Address

End Sub

I bolded (well, tried to bold)the lines I've added that are causeing the problem.

Now I searched the rest of the project for the variables sl, tdl, mtl, al, tl, macl, and mreql, and find them no where else in any code or spread sheet.

So, am I missing something in this function, or is there hidden code I'm going to need to try and find (and if so how?)

Thanks!

 

[BrianB]

Set ovrl = Sheets("Admin").Range("list_ovr") 'this line gives me the error

You need to check that the named range 'list_ovr' does exist in the sheet called 'Admin' (via Insert/Name/Define)