Adding code using VBA

I have created a button that will insert a new row into a table and add a new worksheet. It will also rename the worksheet with whatever was inserted into Cell L7.

I also need the button to insert formulas into certain cells on the new row of the table.

The problem I am having is that the formula must contain a reference to the new worksheet that was created.

My code is below:

Private Sub CommandButton1_Click()
Procedure1
Procedure2
End Sub
 
Sub Procedure1()
sheet_name_to_create = Sheet1.Range("L7").Value




For rep = 1 To (Worksheets.Count)
    If LCase(Sheets(rep).Name) = LCase(sheet_name_to_create) Then
    MsgBox "This pilot already exists"
    Exit Sub
    End If
Next




    Sheets("New Pilot").Copy After:=Sheets(Sheets.Count)
    Sheets(ActiveSheet.Name).Name = sheet_name_to_create








End Sub
 
Sub Procedure2()




Dim the_sheets As Worksheet
Dim table_list_object As ListObject
Dim table_object_row As ListRow




Set the_sheet = Sheets("Summary")
Set table_list_object = the_sheet.ListObjects(1)
Set table_object_row = table_list_object.ListRows.Add




table_object_row.Range(1, 2).Value = Range("L7")




[B]table_object_row.Range(1, 5).Formula = "=LOOKUP(Data!B2,**L7**B9:B373,**L7**N10:N374)"[/B]












End Sub

Where the "**L7**" is, is where I need whatever value was typed into cell L7 to be inserted.

I was asked to create this workbook by my employer, and have just started learning VBA to do it.

Thank you for the help.

Re: Adding code using VBA

Almost! I just need the formula to reference the worksheet that has been named whatever is in cell L7.

So, if "test" is entered into cell L7 and the button is pressed, the following is entered into the respective cell:

=LOOKUP(Data!B2,testB9:B373,testN10:N374)

I need it to be:

=LOOKUP(Data!B2,test!B9:B373,test!N10:N374)

so that it will reference the newly created worksheet.

Re: Adding code using VBA

Works perfectly! Thank you.