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

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,369
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
 

Watch MrExcel Video

Forum statistics

Threads
1,102,783
Messages
5,488,854
Members
407,658
Latest member
Arias610

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top