Or you may want to learn how to use macros????
If you want to try, start a new workbook.
Open the VBE (where macros live, by hitting alt-F11)
Insert|Module (on the top toolbar)
Paste this into the code window that just opened.
Option Explicit
Sub CreateAndRenameTheSheets()
Dim StartDate As Date
Dim EndDate As Date
Dim dCtr As Date
Dim wks As Worksheet
If ActiveWorkbook.Name = ThisWorkbook.Name Then
MsgBox "Please activate the correct workbook first!"
Exit Sub
End If
StartDate = Application.InputBox(Prompt:="Enter a date", _
Default:=Format(Date, "mmm dd, yyyy"), _
Type:=1)
'minor validity/sanity checks
If StartDate < DateSerial(2009, 1, 1) _
Or StartDate > DateSerial(2011, 12, 31) Then
MsgBox "Try later"
Exit Sub
End If
'always start with the first
StartDate = DateSerial(Year(StartDate), Month(StartDate), 1)
'last day of the month with the start date
EndDate = DateSerial(Year(StartDate), Month(StartDate) + 1, 0)
With ActiveWorkbook
For dCtr = EndDate To StartDate Step -1
Select Case Weekday(dCtr)
Case vbSunday, vbSaturday
'skip it
Case Else
Set wks = Worksheets.Add
On Error Resume Next
wks.Name = Format(dCtr, "mmm dd")
If Err.Number <> 0 Then
MsgBox "Rename failed with: " & wks.Name
Err.Clear
End If
On Error GoTo 0
End Select
Next dCtr
End With
End Sub
Now hit alt-f11 to get back to excel.
Save this workbook as:
WorkbookToCreateSheetsBasedOnDates.xls
Then open a test workbook--
Then hit alt-f8 and choose the macro to run.
Enter the date you want (the code will figure out the first of the month and the
last of the month and cycle through those days).
=========
Then whenever you need this to work, just open this workbook with the macro,
activate the correct workbook, hit alt-f8 and you're done!
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.)
