Add a new row to a table when a new sheet is created containing references to that sheet
Results 1 to 3 of 3

Thread: Add a new row to a table when a new sheet is created containing references to that sheet
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Add a new row to a table when a new sheet is created containing references to that sheet

    Hi,

    I have a Workbook which when users fill in a form, in creates a new sheet from a template and fills in some of the sheet from the form. I would also like it to update on the home page with a new row that will reference cells in that new sheet.

    So Currently the user fills in a form and Excel duplicates my blank version of the sheet, and adds there data, this provides them a unique tab to track the progress of there work and works by the following:

    Private Sub cmdAdd_Click()


    Worksheets("Blank").Activate
    ActiveSheet.Copy After:=Worksheets(Sheets.Count)
    ActiveSheet.Name = txtEngNum
    Range("C6").Value = txtEngNum
    Range("C7").Value = txtVT
    Range("C8").Value = txtOwn
    Range("E6").Value = txtFit
    Range("E7").Value = txtVeh
    Range("E8").Value = txtPri


    End Sub

    In the individual sheet for each engine the users track there progress for there current task, I would like my home page to collaborate this, Ie generate new row in a table which contains a formula directing to the sheet that was just created.

    I'm not sure how well I've explained this, but thank you for your help in advanced.

    Alfie

  2. #2
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,593
    Post Thanks / Like
    Mentioned
    62 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Add a new row to a table when a new sheet is created containing references to that sheet

    welcome to the forum

    - you did not tell us which values or formula should be entered in the "new" row on "Hope Page"
    - this should get you started

    Code:
    Private Sub cmdAdd_Click()
        Worksheets("Blank").Activate
        ActiveSheet.Copy After:=Worksheets(Sheets.Count)
        ActiveSheet.Name = txtEngNum
        Range("C6").Value = txtEngNum
        Range("C7").Value = txtVT
        Range("C8").Value = txtOwn
        Range("E6").Value = txtFit
        Range("E7").Value = txtVeh
        Range("E8").Value = txtPri
    'enter new sheet name in first empty cell in column A in Home Page
        With Sheets("Home Page")                    'amend if required 
            .Range("A" & .Rows.Count).End(xlUp).Offset(1) = txtEngNum
        End With
    End Sub

  3. #3
    New Member
    Join Date
    Jul 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Add a new row to a table when a new sheet is created containing references to that sheet

    Hi,

    Thank you for your help, this doesn't quiet do what I need though but I think it's the way I've explained it. The User will fill in a user form on the home page which among other things contains an engine number, when they submit the form a new sheet is generated with the name of that engine number. Within the sheet generated there is an "Engine Status" which the use will update there engine status over time so the workbook is going to be holding tabs containing the status of about 20 engines.

    On the home page is a table which currently updates a new row similar to the code above giving me a new row with the engine number, but I would also like a reference to the new sheet. For example if someone sills in the form for engine 8826, a new tab is created called 8826 and a line for 8826 added to "Home". To do manually I'd obviously type ='8826'!H6 into the row, but i'm not sure how to do this in VBA.
    I'm assuming there's a way of setting a value for the sheet name and having it insert that name into a formula and then paste that formula in but can't quite find how to do it. End result is I would have a table on the home page with a list of engine numbers and their status and individuals update there own individual tabs

    Many thanks for your help.

    Alfie

    Quote Originally Posted by Yongle View Post
    welcome to the forum

    - you did not tell us which values or formula should be entered in the "new" row on "Hope Page"
    - this should get you started

    Code:
    Private Sub cmdAdd_Click()
        Worksheets("Blank").Activate
        ActiveSheet.Copy After:=Worksheets(Sheets.Count)
        ActiveSheet.Name = txtEngNum
        Range("C6").Value = txtEngNum
        Range("C7").Value = txtVT
        Range("C8").Value = txtOwn
        Range("E6").Value = txtFit
        Range("E7").Value = txtVeh
        Range("E8").Value = txtPri
    'enter new sheet name in first empty cell in column A in Home Page
        With Sheets("Home Page")                    'amend if required 
            .Range("A" & .Rows.Count).End(xlUp).Offset(1) = txtEngNum
        End With
    End Sub

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •