[jakester]

Hey guys -

Ok, so I found some code on the Internet that I have been trying to tweak to my liking but I'm having some trouble. Here's what I am trying to do.

I want to create a worksheet for each day of a given month and name the worksheet for each respective day. The naming convention would work like: 10.01.10 Data, 10.02.10 Data, etc.

Also, I would like to create another set of worksheets that are named with the same date convention but with a different name: 10.01.10 Summary, 10.02.10 Summary.

Here's the code I found which helps me solve one part of my problem although the part that adds a new workbook is a little annoying because I would prefer that when I opened up a new workbook in Excel that the macro modify this open workbook instead of opening another workbook. But I'm unsure how to go about the same steps for creating the worksheets titled 10.01.10 Summary.

Here's the code:

Sub CreateNewBook()

Dim WS As Worksheet ', WB As Workbook
Dim MonthX As Date, Control As Variant, DaysInMonth As Byte, I As Byte, NewSheetCount As Byte, OldSheetCount As Byte

OldSheetCount = 1

Control = InputBox("Enter month in the form of mm/yyyy.", "Month Entry", Month(Date) & "/" & Year(Date))
If IsDate(Control) Then
MonthX = CDate(Control)
DaysInMonth = Day(DateSerial(Year(MonthX), Month(MonthX) + 1, 0))
Application.SheetsInNewWorkbook = DaysInMonth
'Set WB = Workbooks.Add
'Set WS = Worksheets.Add
I = 1
For Each WS In ActiveWorkbook.Worksheets
WS.Name = Month(MonthX) & " " & I
I = I + 1
Next
Else
MsgBox "Error while inputing start date."
End If

Application.SheetsInNewWorkbook = OldSheetCount

End Sub

[JBeaucaire]

1) Name the sheet in your workbook that you want to copy TEMPLATE.

2) Then run this shorter version:

Option Explicit

Sub SheetCopy()
Dim strDate As String
Dim NumDays As Long
Dim i As Long
Dim sh As Object
Dim wsBase As Worksheet

On Error GoTo EndIt
    
' The Do statement to captures Month/Year via Input Box
' and return number of days in the month to the NumDays variable

    Do
        strDate = Application.InputBox( _
            Prompt:="Please enter month and year: mm/yyyy", _
            Title:="Month and Year", _
            Default:=Format(Date, "mm/yyyy"), _
            Type:=2)
 
        If strDate = "False" Then Exit Sub
        If IsDate(strDate) Then Exit Do
        If MsgBox("Please enter a valid date, such as ""01/2010""." _
            & vbLf & vbLf & "Shall we try again?", vbYesNo + vbExclamation, _
            "Invalid Date") = vbNo Then End
    Loop
    
    Application.ScreenUpdating = False
    NumDays = Day(DateSerial(Year(strDate), Month(strDate) + 1, 0))
    Set wsBase = Sheets("Template")
    
' For each day, the For statement below copies the template sheet 'n' times

    For i = 1 To NumDays
        wsBase.Copy After:=Sheets(Sheets.Count)
        ActiveSheet.Name = Format(DateSerial(Year(strDate), Month(strDate), i), "mm.dd.yy")
    Next i

EndIt:
    Application.ScreenUpdating = True
End Sub

[jakester]

JB - I use Excel 2007 and when I run your code in my workbook, it doesn't run at all but when I run it in Excel 2003 (compatibility version) it runs fine. Maybe one or more of the command lines are slightly different from 03 to 07?

[JBeaucaire]

Doesn't run at all? Try opening the VBEditor and using F8 to run the code one line at a time, spot the line of code where it does the wrong thing.

2007 means you installed it into an xlsm file, right?

[JBeaucaire]

I had a colleague run it on his xl2007, worked for him, too. Macros enabled on yours?

[jakester]

I'll have to double-check when I'm back at the office tomorrow... yeah, my macros are definitely enabled (I have about 15 of them that I run daily) but for some reason it wasn't running. Strangely, it gave no error either... thx, JB.

[JBeaucaire]

It's not strange that it gives no error. I have an On Error Goto EndIt line in there so when anything goes wrong it exits gracefully.

The F8 trick will reveal that to you and you'll exactly which line of code failed and generated an error.

[ScottGem]

Make sure you are saving the file as an xlsm. I had a similar issue with VBA code not running until I did that.

But I have to ask what the purpose of this spreadsheet is. It sounds to me like you may be ready to move this data up to a real database.

[jakester]

Thanks, Scott. Actually, the purpose of this macro is to create a workbook for each day of the month and dump data into each daily WS. This code saves me from doing it manually.

[jakester]

JB - I read this post after I fixed the problem. Still not sure what the issue was but I got the code to work in an existing module I built. Thx again, that was really helpful stuff.

[ScottGem]

Jakester said Comment on ScottGem's post
Thanks, Scott. Actually, the purpose of this macro is to create a workbook for each day of the month and dump data into each daily WS. This code saves me from doing it manually.

And again I ask what the purpose of this spreadsheet is. If you have separate data by day, this can usually be done more easily in a database than a spreadsheet. Spreadsheets are great and have a definite place, but there frequently comes a time when putting DATA into a database makes more sense.

BTW, please don't use the Comments for follow-up. Use the Answer options instead.

[JBeaucaire]

In general, I have to agree with Scott. But I know it's hard to walk away from Excel after using it for so long. ;)

I also find individual day sheets for data a pretty "humanistic" approach to data management, too. Even if you stay with Excel, I would put all my data into a single "database sheet" in my workbook.

With the data in a single sheet, I can use normal reporting, filtering, charting tools to extract meaningful data from the set as a whole. Your layout makes that near-on a marathon event to extract complex reports. The data is too spread out.

And with the AUTOFILTER, it's a breeze to still see any one day of data in the master database sheet without having to navigate all those individual sheets.