Generate worksheets that are named as dates

[jakefh]

Hi.

I've found some macros that nearly do what I want, but my non-existent vba knowledge means I cannot edit them to do what I want.

I have a worksheet that I want to duplicate onto new sheets and I want the tabs to be named after sequential dates. eg tab 1 would be "template", tab 2 would be "3/3/18", tab 3 would be "10/3/18" etc.

Ideally I would like to have a start & finish date.

I would also like to choose between it repeating every 7 days and every 1 day but I don't mind if this is two separate macros.

Thanks in advance.

 

[Dryver14]

IN a column place the names of the sheets you would like to add, they must be acceptable sheet names for dates something like 01 Mar 18 formatted as text.

select the column and run this macro

Sub CreateMany()


Dim ThisWS As String
Dim Cell As Range


    For Each Cell In Selection
        ThisWS = Cell.Value
        Worksheets.Add after:=Worksheets(Worksheets.Count)
        ActiveSheet.Name = ThisWS
    Next Cell
    
End Sub

 

[My Aswer Is This]

I have made a script that will start on a date and end on another date. But it gets more confusing if you want to start of May 14th and stop on August 25th but then you say I want the dates to advance by 7 days or 4 days or 3 days the user gets to choose how many days to skip.

And then we would need to know how you want the sheet name formatted. Like 12-16-17 or May-16-17 or something different.

 

[Dryver14]

Just noticed with this moving up the board.

If you do decided to put dates in a column and select them, just select the items you want to create sheets for, not the whole column.

 

[jakefh]

Thank you for your time with this.

The only problems are that I need the 1st sheet to be duplicated as it already has cells filled in and if possible I'd like to use the column that dictates the repeated sheets names to be date format rather than text so I can use autofill on the column with the dates in. Is this possible?

 

[jakefh]

I have made a script that will start on a date and end on another date. But it gets more confusing if you want to start of May 14th and stop on August 25th but then you say I want the dates to advance by 7 days or 4 days or 3 days the user gets to choose how many days to skip.

And then we would need to know how you want the sheet name formatted. Like 12-16-17 or May-16-17 or something different.

The name format wouldn't matter as long as it was clearly a date.

Thanks.

 

[My Aswer Is This]

It's easier for us to give you answers if you check back in at least daily. Your original post was nearly a week ago.

So your wanting to copy a sheet named Template

And specify start and stop dates. And advance each sheet name by 1 day. Is that correct?

 

[My Aswer Is This]

I'm assuming the answer to my previous question is yes.
Will copy sheet named Template
Try this script.

Enter start date like this 3/1/18
Enter stop date like this 3/15/18
Enter number days to advance by like this
1 for 1 day or 7 or 10 days etc.
So if you enter 3/1/18 and advance by 5 you would get dates like:
3/1/18 then 3/6/18

Sub Copy_Range()
'Modified 3-4-18 2:35 PM EST
On Error GoTo M
Dim Start As Date
Dim i As Long
Dim Last As Date
Start = InputBox("What Startdate")
Last = InputBox("What Stopdate")
Dim ans As Long
Dim aa As Long
ans = (Last - Start) + 1
ss = MsgBox("You will be making " & ans & "  New sheets  are you sure you want to do this?", vbYesNo)
If ss = "No" Then Exit Sub
aa = InputBox("Advance date by how many days", "Must be 1 or greater", "1")
    For i = 1 To ans
        Sheets("Template").Copy After:=Sheets(Sheets.Count)
        ActiveSheet.Name = Format(DateAdd("d", ((i - 1) * aa), Start), "mm-DD-YY")
    Next
Exit Sub
M:
MsgBox "That sheet name has already been used or you entered a improper date"
End Sub

 

[My Aswer Is This]

Now if you enter
3/1/18 then 3/10/18 this means you will get 10 new sheets.
If you enter a advance of greater then 1 the dates will be greater then 3/10/18.

This is the best I can do for you.

Try it and see how it works.

 

[My Aswer Is This]

I made one mistake. Try this.
And I added a few new touches
When asking for start date todays date will be automatically entered and End date will be automatically set for 10 days later. You can modify these dates.

Sub Copy_Range()
'Modified 3-4-18 3:55 PM EST
On Error GoTo M
Dim Start As Date
Dim i As Long
Dim Last As Date
Start = InputBox("What Startdate", , Date)
Last = InputBox("What Stopdate", , Date + 10)
Dim ans As Long
Dim aa As Long
ans = (Last - Start) + 1
ss = MsgBox("You will be making " & ans & "  New sheets  are you sure you want to do this?", vbYesNo)
If ss = vbNo Then Exit Sub
aa = InputBox("Advance date by how many days", "Must be 1 or greater", "1")
    For i = 1 To ans
        Sheets("Template").Copy After:=Sheets(Sheets.Count)
        ActiveSheet.Name = Format(DateAdd("d", ((i - 1) * aa), Start), "mm-DD-YY")
    Next
Exit Sub
M:
MsgBox "That sheet name has already been used or you entered a improper date"
End Sub