Add a cell reference in newly created worksheets created by VBA indexed by row

[Dionysus]

Goal: add a formula to a specific cell in multiple worksheets that were created and named with VBA. Formula should follow the same logic as was used to create the worksheets and index down the rows until a blank cell is reached.

Resolution Attempt History: I have tried modifying the VBA I use to create the new worksheets based on the data in a column and Googling and searching this site for solutions in whole, or in part, with little success.

Software: I am using Office 365 on Windows 7.

The VBA I currently have that creates and names the worksheets is as follows:

Sub Create_New_Sheets()
Dim MyNames as Range, MyNewSheet as Range
Sheet9.Visible = True
Sheet1.Select
Set SourceData = Sheet9
Set MyNames = Sheet2.Range("A2:A76")
For Each MyNewSheet In MyNames.Cells
    If MyNewSheet = vbNullString Then Exit For
    SourceData.Copy After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)
   ActiveSheet.Name = MyNewSheet.Value
Next MyNewSheet
MyNames.Worksheet.Select
Sheet9.Visible = False
Sheet1.Select
Sheet2.Visible = False
Sheet1.Select
Range("A2").Select

Expected outcome: Duplicate the logic of the above code that creates and names the worksheets by indexing down a column until the first blank cell is reached but, this time, instead of putting the result of each respective cells source data in the name of the worksheet I want to put the name in a cell and index through each worksheet and put the next name down the Excel column until the first blank cell is reached and then exit the routine. Each worksheet that is created is a nine digit long product code that I can then pull to a cell with =MID(CELL("FILENAME",A1),FIND("]",CELL("FILENAME"A1))+1,255). What I'm struggling with is pulling in the description of the product code in a specific cell in each newly created worksheet.

Please let me know if any further information, and/or clarification, is required or if a copy of the subject workbook would be of benefit to upload.

Regards,

Bill