Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Insert Worksheet with Worksheet Name the Current Date
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. -- Thanks. Steve |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Insert Worksheet with Worksheet Name the Current Date
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 he 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.) Steve wrote: 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. -- Thanks. Steve -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Rename new worksheet with the current date | Excel Programming | |||
Function to insert current filename into worksheet? | Excel Worksheet Functions | |||
I can't insert a worksheet into my current spreadsheet file | Excel Worksheet Functions | |||
Go to current date upon opening Worksheet | Excel Programming | |||
Help with Macro or VBA script - Insert current Time/Date for different records | Excel Programming |