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.