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

[Ebgb6]

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

 

[Yongle]

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

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
[I][COLOR=#006400]'enter new sheet name in first empty cell in column A in Home Page[/COLOR][/I]
    With Sheets("[COLOR=#ff0000]Home Page[/COLOR]")                   [I] 'amend if required [/I]
        .Range("A" & .Rows.Count).End(xlUp).Offset(1) = txtEngNum
    End With
End Sub

 

[Ebgb6]

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

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

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
[I][COLOR=#006400]'enter new sheet name in first empty cell in column A in Home Page[/COLOR][/I]
    With Sheets("[COLOR=#ff0000]Home Page[/COLOR]")                   [I] 'amend if required [/I]
        .Range("A" & .Rows.Count).End(xlUp).Offset(1) = txtEngNum
    End With
End Sub