Forum Discussion
Macro
I want to create a macro that creates a new sheet and pulls from the most recent sheet I was working on.
So I have a worksheet named 5/3/2018 I want to use as a template to create a new sheet named 5/10/2018. The following week I want 5/10/2018 to be my new template and I want to create a new sheet named 5/17/18, etc. How do I do this. Is it possible?
Hi Julie,
Are you sure that the worksheets are named in this way "5/3/2018"?
In fact, the forward slashes are not allowed in the worksheets name!
If they are named in this way "5 3 2018", you can depend on this macro:
Sub AddDatedWorksheets()
'To add a dated worksheet on weekly basis
'Written by Haytham Amairah
'Last update: 4/20/2018
On Error GoTo Handler
Application.ScreenUpdating = False
Dim startDate As Date
startDate = DateValue(ThisWorkbook.ActiveSheet.Name)
Dim nextDate As Date
nextDate = DateAdd("ww", 1, startDate)
With ThisWorkbook.ActiveSheet
.Copy After:=Sheets(.Index)
End With
ThisWorkbook.ActiveSheet.Name = Month(nextDate) & " " & Day(nextDate) & " " & Year(nextDate)
Application.ScreenUpdating = True
Exit Sub
Handler:
MsgBox ("The name of the worksheet isn't valid!")
Application.ScreenUpdating = True
End SubPlease find it in the attached file.
Hope that helps
Haytham
This helps a lot!! Thanks
My sheets are actually named 5-3-2018, 5-10-2018, 5-17-2018, etc. May I use that in the macro?Hi Julie,
In this case, all you have to do is:
Replace this line:
ThisWorkbook.ActiveSheet.Name = Month(nextDate) & " " & Day(nextDate) & " " & Year(nextDate)
With this:
ThisWorkbook.ActiveSheet.Name = Month(nextDate) & "-" & Day(nextDate) & "-" & Year(nextDate)
I hope this helps you
Haytham
