Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input date function.
Hi, The following code are run and creat many sheets depand on the how many days of the current month. But instead of just ONLY creat the current month, I would like to have an option with the message box to ask me which month that I like to creat the form. Then it will creat the sheets according to the input date I provide. How do I do that? Sub createsheets() If MsgBox( "Are you very very sure you want to do this?", vbYesNo, "Create LOTS of Sheets") = vbYes Then For i = 1 To Day(DateSerial(Year(Date),Month(Date + 1, 0)) Sheets("Sheet1").Copy Befo=Worksheet (Worksheets.Count) ActiveSheet.Name = i Range("F3").Value = DateSerial(Year(Date), _ Month(Date) + 0, i) Next End If End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input date function.
hi Hume
good to see you again change the code to Sub createsheets() Dim s_Date As Date If MsgBox("Are you very very sure you want to do this?", vbYesNo, "Create LOTS of Sheets") = vbYes Then s_Date = InputBox("Enter starting date in the format dd/mm/yy?") 'change the format to what ever is applicable for your region For i = 1 To Day(DateSerial(Year(s_Date), month(s_Date) + 1, 0)) Sheets("Sheet1").Copy Befo=Worksheets(Worksheets.Count) ActiveSheet.Name = i Range("F3").Value = DateSerial(Year(s_Date), _ month(s_Date) + 0, i) Next End If End Sub -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "hume" wrote in message ... Hi, The following code are run and creat many sheets depand on the how many days of the current month. But instead of just ONLY creat the current month, I would like to have an option with the message box to ask me which month that I like to creat the form. Then it will creat the sheets according to the input date I provide. How do I do that? Sub createsheets() If MsgBox( "Are you very very sure you want to do this?", vbYesNo, "Create LOTS of Sheets") = vbYes Then For i = 1 To Day(DateSerial(Year(Date),Month(Date + 1, 0)) Sheets("Sheet1").Copy Befo=Worksheet (Worksheets.Count) ActiveSheet.Name = i Range("F3").Value = DateSerial(Year(Date), _ Month(Date) + 0, i) Next End If End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input date function.
Sub createsheets()
Dim dt as Date Dim dt1 as Date Dim lngMonth as Long, i as Long If MsgBox( "Are you very very sure you want to do this?", vbYesNo, "Create LOTS of Sheets") = vbYes Then lngMonth = Application.InputBox("Enter a month number from" & _ " 1 to 12 Inclusive",type:=1) if lngMonth < 1 or lngMonth 12 then exit sub dt = DateSerial(Year(date),lngMonth+1,0) dt1 = DateSerial(Year(date),lngMonth,0) For i = 1 To Day(dt) Sheets("Sheet1").Copy Befo=Worksheet _ (Worksheets.Count) ActiveSheet.Name = i Range("F3").Value = dt1 + i Next End If End Sub This assumes you will always create for a month in the current year. -- Regards, Tom Ogilvy "hume" wrote in message ... Hi, The following code are run and creat many sheets depand on the how many days of the current month. But instead of just ONLY creat the current month, I would like to have an option with the message box to ask me which month that I like to creat the form. Then it will creat the sheets according to the input date I provide. How do I do that? Sub createsheets() If MsgBox( "Are you very very sure you want to do this?", vbYesNo, "Create LOTS of Sheets") = vbYes Then For i = 1 To Day(DateSerial(Year(Date),Month(Date + 1, 0)) Sheets("Sheet1").Copy Befo=Worksheet (Worksheets.Count) ActiveSheet.Name = i Range("F3").Value = DateSerial(Year(Date), _ Month(Date) + 0, i) Next End If End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input date function.
Sub createsheets()
If MsgBox( "Are you very very sure you want to do this?", _ vbYesNo, "Create LOTS of Sheets") = vbYes Then szMonth = Inputbox("Supply month number (1-12)") szDays = Day(DateSerial(Year(Date),szMonth, 0)) For i = 1 To szDays Worksheets("Sheet1").Copy Befo= _ Worksheet(Worksheets.Count) ActiveSheet.Name = i Range("F3").Value = DateSerial(Year(Date), szMonth, i) Next i End If End Sub -- HTH RP (remove nothere from the email address if mailing direct) "hume" wrote in message ... Hi, The following code are run and creat many sheets depand on the how many days of the current month. But instead of just ONLY creat the current month, I would like to have an option with the message box to ask me which month that I like to creat the form. Then it will creat the sheets according to the input date I provide. How do I do that? Sub createsheets() If MsgBox( "Are you very very sure you want to do this?", vbYesNo, "Create LOTS of Sheets") = vbYes Then For i = 1 To Day(DateSerial(Year(Date),Month(Date + 1, 0)) Sheets("Sheet1").Copy Befo=Worksheet (Worksheets.Count) ActiveSheet.Name = i Range("F3").Value = DateSerial(Year(Date), _ Month(Date) + 0, i) Next End If End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How input today's date and keep that date | Excel Worksheet Functions | |||
input a date or update it based on date in another cell | New Users to Excel | |||
date input | Excel Discussion (Misc queries) | |||
input date as date in formula | Excel Programming | |||
input date as date in formula | Excel Programming |