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:
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.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
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