Solved: insert date with vba

[CCkfm2000]

Hi all,

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

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]

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

insert where?

[CCkfm2000]

starting in cell a5 going down.

[lucas]

I'm sorry, I don't follow.

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

[Bob Phillips]

[vba]

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
[/vba]

[CCkfm2000]

thanks xld,

i've attached a copy for reference.

[CCkfm2000]

can this code put the months on order?

[Bob Phillips]

Are you saying my code did/did not work?

What exactly are you saying in the last post?

[CCkfm2000]

yes it worked, thanks. just asking if the worksheets can be sorted in order of month?

[SamT]

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]

Just change it to

[vba]

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
[/vba]

[CCkfm2000]

thats solved it.

Thanks to all....