Programmatically reference cells in a newly created worksheet

  • I've been at this for entirely too long. As much as I want to do this myself, I have resorted to asking for help.

    I have a User Form, a "Data" sheet(for quick reference and a Pivot Table), a template, and individually named worksheets.


    Here's the process:

    User Form gets data

    The data is added to the next available row in the table on "Data"
    A new worksheet is created and named the person's full name.

    A template is copied over

    Data from the User Form is then put into the corresponding cells on the newly named sheet.

    I have a Sub that creates links in the table on the "Data" worksheet to the corresponding named worksheets.


    ex. Bob Smith's information is entered into the User Form. A new line is created on the table in the "Data" worksheet with all of that information. A new worksheet is created and named "Bob Smith", and then the template is copied over to it. Bob's information is then entered into the appropriate cells on his sheet.


    So far, so good.


    I would like the rest of the cells in the table on the "Data" worksheet to to reference their corresponding cells in the newly created worksheet. Meaning, If there is a change on Bob's sheet, it will change accordingly on the "Data" sheet.


    I've gone in many circles, but have not been able to pin down what I could do. Any help would be great!


    • Best Answer

    Where exactly are you stuck? You have the name of the sheet, and it sounds like you want a simple formula link, so you'd just be adding a formula using code like:


    Code
    somecell.formula = "='" & FullName & "'!A1"

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • It's easily done. :)


    Glad we could help.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

Participate now!

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