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
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,580
Office Version
365
Platform
Windows
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
 

Ebgb6

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

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,542
Messages
5,511,938
Members
408,869
Latest member
MM005

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top