Macro to Create Dated Worksheets

Ok, I want to do a couple things.

I have a daily report that needs to be filled out, and it is the same for each day.

I want to create a macro that will create 365 dated tabs, and at the same time, I want each of those dated tabs to copy the original template.

So far I have found a macro that will create dated tabs in the format of mm-dd-yyyy, but I can't figure out how to copy the data/formats from my original template.

Also, I have a date column in the template, and I want the date column to pull the date from the dated tab name.

Can anyone help?!?!?!

Welcome to the forum, Noah.

If you're going to do any analysis of the data that crosses day boundaries, you should have all data on a single tab with a column for date. That's how Excel is designed to work.

Sounds like you really just need to copy your template sheet.

Sheets("Template").Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = Format(Date, "MM-DD-YYYY")

The make further changes to the new sheet as needed...


Since your tabs ARE dates, you really just want to display the tab name in the date column on those new sheets, yes? You can do that with this formula:

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)

Thanks for the responses guys. I'm not actually analyzing data really, its a daily activity report for my friend that works at the Ritz Carlton. Here was a macro that I found that does what I want, except 1: It doesn't Create the Sheets in the same workbook, it creates a new one and 2: I had previously tried the mid formula above to reference the sheet name in the cell, but I got a #VALUE error.

Option Explicit
Sub testme()
   Dim iCtr As Long
   Dim NewWkbk As Workbook
   Dim HowMany As Long
   Dim StartDate As Date
   
   StartDate = DateSerial(Year(Date), Month(Date) + 1, 1)
   StartDate = Application.InputBox _
               (prompt:="Enter a date in the month you want", _
               Type:=1, Default:=Format(StartDate, "mmmm dd, yyyy"))
 
   If Year(StartDate) < 2005 _
    Or Year(StartDate) > 2010 Then
       Exit Sub
   End If
   'First of the month!
   StartDate = DateSerial(Year(StartDate), Month(StartDate), 1)
   HowMany = Day(DateSerial(Year(StartDate), Month(StartDate) + 1, 0))
   
   Set NewWkbk = Workbooks.Add(1) 'single sheet
   NewWkbk.Sheets.Add Count:=HowMany - 1
         
   For iCtr = 1 To HowMany
       NewWkbk.Worksheets(iCtr).Name _
           = Format(StartDate - 1 + iCtr, "yyyy_mm_dd")
   Next iCtr
       
End Sub

This one actually only created tabs by month, but that is fine. My friend just wants do create each month at a time without a whole lot of work because the employees using the spreadsheet aren't good with excel, so he didn't want to have them have to copy the sheets each day and set them up.

I attached a copy of the sheet that I want to duplicate

Here's a slight rewrite of this... it simply asks for the starting date and how many sheets you want, then it creates them in the active workbook:

Option Explicit

Sub AddDatedSheets()
Dim NewSh    As Long
Dim HowMany  As Long
Dim Start    As Date
   
    Start = Application.InputBox(prompt:="Enter the starting date for new sheets", _
                Type:=1, Default:=Format(Date, "mmmm dd, yyyy"))
    If Year(Start) < 2005 Or Year(Start) > 2010 Then Exit Sub
   
    HowMany = Application.InputBox(prompt:="How many sheets to create?", _
                Type:=1, Default:=10)
    If HowMany = 0 Then Exit Sub
   
    For NewSh = 1 To HowMany
        Worksheets.Add(After:=Worksheets(Worksheets.Count)) _
             .Name = Format(Start - 1 + NewSh, "yyyy_mm_dd")
    Next NewSh
       
End Sub

You asked that already and I answered in post #3. That's the code for that, replace the Worksheets.Add from this macro with the construct from post #3 and edit it for your needs.


By the way, the formula I gave you in that same post only displays the proper answer AFTER you save the workbook at least once.

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.