Dynamically Generate Formula’s Based On New Worksheet Names

Re: Dynamically Generate Formula’s Based On New Worksheet Names

Column "A" on "Main Sheet" will contain a formula that identifies the sheet that the rest of the formulas in that row should reference. When a new sheet is inserted, the last row in "Main Sheet" will be copied and the old sheet name from column "A" is used in a search and replace with the new sheet name.
Put a heading in cell A1 in "Main Street", save the workbook, then copy and paste the following into the ThisWorkbook module:

Private Sub Workbook_NewSheet(ByVal Sh As Object)
    Dim NewRowCell As Range
     
    Set NewRowCell = Sheets("Main Sheet").Range("A65536").End(xlUp).Offset(1, 0)
    NewRowCell.Offset(-1, 0).EntireRow.Copy Destination:=NewRowCell
    NewRowCell.EntireRow.Replace What:=NewRowCell.Offset(-1, 0).Value, Replacement:=Sh.Name, LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    NewRowCell.FormulaR1C1 = "=MID(CELL(""filename""," & Sh.Name & _
        "!RC), FIND(""]"",CELL(""filename""," & Sh.Name & "!RC))+1,31)"
End Sub

You should now be able to insert a sheet and have the formulas changed in a new row in "Main Sheet"

Re: Dynamically Generate Formula’s Based On New Worksheet Names

peggydeee,
Not sure why you are getting the error. What version of Excel are you using? You could try exporting, then importing the code in your modules as Excel sometimes doesn't clean up debugging code correctly and will cause errors.

I have attached a file that works for me. It contains exactly the same code as above. Insert a sheet by right-clicking a tab -> Insert -> OK. Let me know if it works for you.

Re: Dynamically Generate Formula’s Based On New Worksheet Names

Try replacing the lines for the .Replace method with the following code. It looks like additional options (that you don't need) were added to excel 2003 from 2000 for this command.

NewRowCell.EntireRow.Replace What:=NewRowCell.Offset(-1, 0).Value, _
    Replacement:=Sh.Name, LookAt:=xlPart, MatchCase:=False

BTW, your MS Office version in your Profile says 2003. You should probably change it to 2000.

Re: Dynamically Generate Formula’s Based On New Worksheet Names

Hey friend, see if the new attachment works.

Re: Dynamically Generate Formula’s Based On New Worksheet Names

You made my day. One more tip. On your Main Sheet be sure your formulas use absolute referencing (i.e. $A$1, not A1). If it is A1 it will change to A2 when copying, which you probably don't want.