How to create a macro to summarise data when new worksheet is created
Category: [Excel] Demo Available
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.
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.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to use a Macro to copy rows from multiple worksheets based on a cell value greater than zero |
| How to use Excel VBA macro to convert multiple columns to multiple rows |
| How to create and auto run macro if value on cell A1 is less than value on B2 |
| How to use a macro to copy rows from multiple worksheets based on a cell value greater than zero |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.