How to Macro required to summarise data when new worksheet is created

How to create a macro to summarise data when new worksheet is created

Requirement:

The user has created a very simple timesheet template on a worksheet and have used some code that copies this worksheet (named 'Template') and renames it with the week commencing date.

The user wants that once submitted, the new data to be added to a summary table on a separate worksheet named 'summary'. The user has attached the document - please refer to the link below.

https://www.ozgrid.com/forum/forum/help-forums/excel-vba-macros/1194491-macro-required-to-summarise-data-when-new-worksheet-is-created

Solution:

Code:

Sub Copyrenameworksheet()

    Dim ws As Worksheet
    Dim vInput
    Dim lRw As Long
    vInput = InputBox("Please enter date for new timesheet, format dd.mm.yyyy", "New Timesheet")
    If Len(vInput) = 0 Then
        MsgBox "User cancelled"
        Exit Sub
    Else
        With Sheets("Template")
            .Copy After:=Worksheets(Sheets.Count)
            ActiveSheet.Name = vInput
            ActiveSheet.Range("Q3").Value = vInput

            With Sheets("Summary")
                lRw = .Cells(.Rows.Count, 2).End(xlUp).Row + 1
                .Cells(lRw, 2).Value = vInput
                .Cells(lRw, 3).FormulaR1C1 = "=INDIRECT(""'""&RC[-1]&""'!""&""$O$35"")"
            End With

        End With
    End If
End Sub

Obtained from the OzGrid Help Forum.

Solution provided by royUK.