Macro to Insert Worksheet with Worksheet Name the Current Date

[Steve]

I have a need to generate a spreadsheet (timesheets for recording time) that
will do the following:
1. Create a worksheet in the spreadsheet that has as the date in the
worksheet the date from the previous worksheet plus one day (incremental adds
the next day's timesheet).
2. Name the worksheet the date. So if the date in say cell A1 was 2/10/10
the name of the worksheet would be something like 2-10-10 or 2/10/10.
3. As an added wish, I'd like to be able to tell the system how many days I
want and what the starting date would be. So if I told the system to start
on 2/1/10 and that the number of days to setup was 28, I'd get a spreadsheet
with all of the 28 days of February as individual worksheets in my
spreadsheet and the date of each worksheet would be a particular day in
February and the name of each worksheet would be that day.

I am not sure whether this is even possible since I am not an expert in
programming Excel, but figure someone out there would know if it is possible
and if possible how I can program the system to do what I am proposing.

 

[Dave Peterson]

Maybe...

Option Explicit
Sub testme()

Dim LastWks As Worksheet
Dim StartDate As Date
Dim HowMany As Long
Dim NewWks As Worksheet
Dim dCtr As Long

With ActiveWorkbook
Set LastWks = .Worksheets(.Worksheets.Count)
On Error Resume Next
StartDate = LastWks.Range("A1").Value2 + 1
If Err.Number <> 0 Then
Err.Clear
MsgBox "Check the value in A1 of:" _
& vbLf & LastWks.Name _
& vbLf & "It doesn't look like a date"
Exit Sub
End If
On Error Resume Next
End With

'some sanity check
If Year(StartDate) < Year(Date) Then
'too early
MsgBox "Check the value in A1 of:" _
& vbLf & LastWks.Name _
& vbLf & "It doesn't look right."
Exit Sub
End If

'just finish the month??
HowMany = DateSerial(Year(StartDate), Month(StartDate) + 1, 1) _
- StartDate

HowMany = Application.InputBox _
(Prompt:="How many days", _
Title:="Start Date is: " & Format(StartDate, "mmm dd, yyyy"), _
Default:=HowMany, _
Type:=1)

'some sanity checks
If HowMany < 1 _
Or HowMany > 100 Then
MsgBox "That doesn't sound right"
Exit Sub
End If

For dCtr = StartDate To StartDate + HowMany - 1
Set NewWks = Worksheets.Add(after:=Sheets(Sheets.Count))
With NewWks.Range("A1")
.NumberFormat = "mmm dd, yyyy"
.Value = dCtr
End With
On Error Resume Next
NewWks.Name = Format(dCtr, "yyyy-mm-dd")
If Err.Number <> 0 Then
Err.Clear
MsgBox "Error renaming: " & NewWks.Name
End If
On Error GoTo 0
Next dCtr

End Sub

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)