Dynamically Generate Formula’s Based On New Worksheet Names

  • I am trying to create a yearly trade show sales workbook.
    Each worksheet represents a show except for the Main Sheet where I would like to pull all my totals from each of the trade show sheets into. I have also created an index worksheet at the beginning of the workbook.
    I want to have the “Main Sheet” automatically add a new line each time I add a new sheet to the work book – I would also like it to sub-in the appropriate “sheet name” in each of the formula’s so that all that’s required to keep the workbook up to date is to drop in the new trade show worksheet.
    Thanks for any help you can provide!

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

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

    Bert
    The one with the fewest lines of code wins

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


    Thanks, I appreciate the help - unfortunately I'm perhaps a little thick! I can't get the code to work - I keep trying different ways but it's just not working out for me:-(
    One error I get every time I add a new work sheet is "Named Argument Not Found" any idea's?

  • 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


    Nope it's the same error with that - It's highlighting the line " SearchFormat:= "
    I'm using Excell 2000
    BTW Thanks agian for helping me:-)

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

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

    Bert
    The one with the fewest lines of code wins

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


    Hey there new friend - still not working (I must be doing somthing wrong) now it does nothing when I add a new worksheet (no errors there) no new line either. But if I close and re-open the work book I get an error that says
    Complie Error
    Syntax Error

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

    Bert
    The one with the fewest lines of code wins

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!