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

Ebgb6

New Member
Joined
Jul 22, 2019
Messages
2
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
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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
[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
 
Upvote 0
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

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
[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
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,037
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top