Creating a new row automatically in a table when a new sheet is created.

[SignMan]

Hello!
I am looking for a solution to prevent manual data entry all the time.
So, I have created a workbook where I manage jobs I am working on. Every sheet is an individual job where I make comments, notes and logs about the job. On my first sheet I have a table that is a summary page essentially. So, I have a formula that pulls information from specified cells on each sheet based on the first column that is the reference to the sheet name (which I have to manually set up and link using a hyperlink).

Is there a way to automatically populate a new row every time I create a new sheet where the formulas copy down automatically?

 

[keef2]

You would have to use VBA I believe. This way you can essential determine the range of data you want to .copy and .pastespecial.

This is a crude example of some code i used to input a row at the bottom of table which was identified by the Row() function. Once established this would copy and past formula and formates to the added row automatically. I used a helper row since i wanted to summarize everything no matter how many rows were added by users.

Sub AddInputRow()
    subName = "AddInputRow" 'For Error handling only

    On Error GoTo Nope

    'Get active sheet
    Dim act As Worksheet
    Set act = ThisWorkbook.ActiveSheet

    'prompt for user entry and loop back if invalid
    StrPrompt = "Enter number of rows to insert:"

Redo:
    xNum = Application.InputBox(StrPrompt, "Insert Rows at Bottom", , , , , , Type:=1)
    If xNum = 0 Or xNum = "" Or xNum = vbNullString Then
        'User Cancelled and close box
    ElseIf xNum < 1 Or Int(xNum) / xNum <> 1 Then
        'User entered a non-positive integer
        GoTo Redo
    Else
'Add rows Main Table and update formatting and formulas
    bot_row = act.Range("Z1")
    act.Rows(bot_row & ":" & bot_row + (xNum - 1)).Insert Shift:=x1ShiftDown
    act.Range("A" & bot_row - 1 & ":Q" & bot_row - 1).Copy
    act.Range("A" & bot_row & ":Q" & bot_row + (xNum - 1)).PasteSpecial xlPasteFormats
    act.Range("A" & bot_row - 1 & ":B" & bot_row - 1 & ":C" & bot_row - 1 & ":D" & bot_row - 1 & ":E" & bot_row - 1 & ":F" & bot_row - 1 & ":G" & bot_row - 1 & ":H" & bot_row - 1 & ":I" & bot_row - 1 & ":J" & bot_row - 1 & ":K" & bot_row - 1 & ":L" & bot_row - 1 & ":M" & bot_row - 1 & ":N" & bot_row - 1 & ":O" & bot_row - 1 & ":P" & bot_row - 1 & ":Q" & bot_row - 1).Copy
    act.Range("A" & bot_row & ":B" & bot_row & ":C" & bot_row & ":D" & bot_row & ":E" & bot_row & ":F" & bot_row & ":G" & bot_row & ":H" & bot_row & ":I" & bot_row & ":J" & bot_row & ":K" & bot_row & ":L" & bot_row & ":M" & bot_row & ":N" & bot_row & ":O" & bot_row & ":P" & bot_row & ":Q" & bot_row + (xNum - 1)).PasteSpecial xlPasteFormulas
    Application.CutCopyMode = False
    act.Range("A" & bot_row).Activate
    ActiveWindow.ScrollRow = bot_row - 10
    
'Add rows Sales Year & PM Breakout Table and update formatting and formulas
    bot_row = act.Range("Z4")
    act.Rows(bot_row & ":" & bot_row + (xNum - 1)).Insert Shift:=x1ShiftDown
    act.Range("A" & bot_row - 1 & ":Q" & bot_row - 1).Copy
    act.Range("A" & bot_row & ":Q" & bot_row + (xNum - 1)).PasteSpecial xlPasteFormats
    act.Range("A" & bot_row - 1 & ":B" & bot_row - 1 & ":C" & bot_row - 1 & ":D" & bot_row - 1 & ":E" & bot_row - 1 & ":F" & bot_row - 1 & ":G" & bot_row - 1 & ":H" & bot_row - 1 & ":I" & bot_row - 1 & ":J" & bot_row - 1 & ":K" & bot_row - 1 & ":L" & bot_row - 1 & ":M" & bot_row - 1 & ":N" & bot_row - 1 & ":O" & bot_row - 1 & ":P" & bot_row - 1 & ":Q" & bot_row - 1).Copy
    act.Range("A" & bot_row & ":B" & bot_row & ":C" & bot_row & ":D" & bot_row & ":E" & bot_row & ":F" & bot_row & ":G" & bot_row & ":H" & bot_row & ":I" & bot_row & ":J" & bot_row & ":K" & bot_row & ":L" & bot_row & ":M" & bot_row & ":N" & bot_row & ":O" & bot_row & ":P" & bot_row & ":Q" & bot_row + (xNum - 1)).PasteSpecial xlPasteFormulas
    Application.CutCopyMode = False
    act.Range("A" & bot_row).Activate
    ActiveWindow.ScrollRow = bot_row - 10
    
End If

Continue:
    'Calculate everything once when finished and enable events
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Exit Sub
    
'Do this if error occurs
Nope:
    MsgBox "An error has been logged: " & vbNewLine & ThisWorkbook.ActiveSheet.Name & vbNewLine & subName & "(Line " & Erl & ")" & vbNewLine & Err.Description
    Resume Continue
    
End Sub