PDA

View Full Version : Solved: insert date with vba



CCkfm2000
03-25-2010, 03:43 PM
Hi all,

What i'm looking for is same help with the following. :help

Ask user what month & year which I can do.

Create a new worksheet and insert the date for that month.

Then create a new worksheet and insert the next month dates and so on for the next 12 months.

Thanks

lucas
03-25-2010, 03:54 PM
Create a new worksheet and insert the date for that month.


insert where?

CCkfm2000
03-25-2010, 03:58 PM
starting in cell a5 going down.

lucas
03-25-2010, 05:03 PM
I'm sorry, I don't follow.

could you post a workbook with an example of what you want?

Bob Phillips
03-25-2010, 05:21 PM
Public Sub DateDrivenData()
Dim sh As Worksheet
Dim mYear As Long
Dim mMonth As Long
Dim mDate As Date
Dim i As Long, j As Long

'get year and month, I will pre-load to test
mYear = 2008
mMonth = 3

For i = mMonth To mMonth + 11

mDate = DateSerial(mYear, i, 1)
Worksheets.Add
ActiveSheet.Name = Format(mDate, "yyyy-mmm")
For j = 0 To 30

Range("A5").Offset(j, 0).Value = mDate + j
If Month(mDate + j) <> Month(mDate + j + 1) Then Exit For
Next j
Next i
End Sub

CCkfm2000
03-26-2010, 03:33 AM
thanks xld,

i've attached a copy for reference.

CCkfm2000
03-26-2010, 04:12 AM
can this code put the months on order?

Bob Phillips
03-26-2010, 04:38 AM
Are you saying my code did/did not work?

What exactly are you saying in the last post?

CCkfm2000
03-26-2010, 04:40 AM
yes it worked, thanks. just asking if the worksheets can be sorted in order of month?

SamT
03-26-2010, 05:52 AM
It will take much longer to compute how to do that with VBA than to do it with formulas and do it by hand.

Put this Formula in Cell "A8" and Fill, Series down to the end of month cell. Change the date value in the Formula for each month.


=DATEVALUE("1-Mar-2010")


Then put this formula in Cell "B8" and copy down to the end of month Cell.


=IF(WEEKDAY(A8,2)=1,"Mon",IF(WEEKDAY(A8,2)=2,"Tue",IF(WEEKDAY(A8,2)=3,"Wed",IF(WEEKDAY(A8,2)=4,"Thu",IF(WEEKDAY(A8,2)=5,"Fri","")))))


See the "Mar 10" sheet in the attachment.

Bob Phillips
03-26-2010, 07:58 AM
Just change it to



Public Sub DateDrivenData()
Dim sh As Worksheet
Dim mYear As Long
Dim mMonth As Long
Dim mDate As Date
Dim i As Long, j As Long

'get year and month, I will pre-load to test
mYear = 2008
mMonth = 3

For i = mMonth To mMonth + 11

mDate = DateSerial(mYear, i, 1)
Worksheets.Add After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = Format(mDate, "yyyy-mmm")
For j = 0 To 30

Range("A5").Offset(j, 0).Value = mDate + j
If Month(mDate + j) <> Month(mDate + j + 1) Then Exit For
Next j
Next i
End Sub

CCkfm2000
03-26-2010, 08:06 AM
thats solved it.

Thanks to all....