VBA macro help for project gantt chart timeline

[deekar]

I am creating a gantt chart that I hope to use as a template for future projects.
Cell C4 contains todays date
Cell C5 contains project start date
Cell C6 contains the deadline or end date
E6 contains a formula to caclulate the number of weeks to complete project.


I'd like a macro that would copy the start date from C5 and paste it into M8 then add 1 day to the adjacent columns (i.e., N8=M8+1; O8=N8+1, etc.) until it has enough days to equal the number of weeks to finish the project and then add 2 more weeks.

For instance start date of 10/22/13 end date of 3/3/13 duration is 19 weeks so i'd like 21 weeks worth of dates into adjacent columns (cells M8-ES8).

It is possible to put this into a vba code? I am hoping to take a class in VBA next year but until then, I could use the help.
Thank you in advance.

 

[Zakkaroo]

The below works for me on a blank, test sheet. I had the code run when I clicked a button ... i.e. insert a command button from the developer tab and copy / paste the code in directly. If you want to run it in another way and you aren't sure how, let me know ... buttons are just a habit I've gotten into.

The only caveat is that the bit in Bold / Red is something I added in to your original sheet .... in Cell E5 I put the formula "=C6+14" . This gives you the end date + 2 weeks. I've then used that value to determine when to STOP adding dates in.

Any problems, let me know!

Range("N8").Name = "projectDate"
Range("M8").Value = Range("C5").Value

Do While Range("projectDate").Offset(0, -1).Value < Range("[COLOR=#ff0000][B]E5[/B][/COLOR]").Value
    
    Range("projectDate").Value = Range("projectDate").Offset(0, -1).Value + 1
    Range("projectDate").Offset(0, 1).Name = "projectDate"

Loop
    
Range("M8").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.NumberFormat = "m/d/yyyy"

 

[deekar]

This works quite well thanks. Can I ask for a bit more? I did change the year into YY format, but I would like to change the font to Arial Narrow 6.0 and then adjust the column width to .3 I created a macro (see below for vba) but I don't know where I would insert it and if there is anything I can remove from the macro language/vba code.

Sub Macro1()
'
' Macro1 Macro
'
'
Rows("6:6").Select
With Selection.Font
.Name = "Arial Narrow"
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
With Selection.Font
.Name = "Arial Narrow"
.Size = 6
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Columns("L:GR").Select
Selection.ColumnWidth = 0.3
End Sub