Putting A Date Into A Cell

[drums4monty]

Hi

I have a spreadsheet with four tabs. I would like to place todays (=TODAY()) date in cell D3 of each of these tabs. I already run a programme on the spreadsheet which formats the sheet etc. how I want it before printout.

Can anyone help?

Alan

[drums4monty]

Hi All

Wow!!! I must be learning something from you guys as I have just solved the problem myself. I know it wasnt that difficult but to a novice like me it a big step forward.

Alan

[geekgirlau]

Why not post your solution - it could help someone else in the future!

[drums4monty]

Ok, here it is but I doubt it is ground breaking and there is probably a better way to do it but hey, it works.

Sub DateInsert()
'
Sheets("Sheet1").Select
Range("D3").Select
ActiveCell.FormulaR1C1 = "=TODAY()"
Selection.NumberFormat = "d-mmm-yy"
Sheets("Sheet2").Select
Range("F3").Select
ActiveCell.FormulaR1C1 = "=TODAY()"
Selection.NumberFormat = "d-mmm-yy"
Sheets("Sheet3").Select
Range("D3").Select
ActiveCell.FormulaR1C1 = "=TODAY()"
Selection.NumberFormat = "d-mmm-yy"
Sheets("Sheet4").Select
Range("D3").Select
ActiveCell.FormulaR1C1 = "=TODAY()"
Selection.NumberFormat = "d-mmm-yy"

End Sub

Alan

[Bob Phillips]

You can tidy that up

[vba]

Sub DateInsert()
'
With Sheets("Sheet1").Range("D3")
.FormulaR1C1 = "=TODAY()"
.NumberFormat = "d-mmm-yy"
End With
With Sheets("Sheet2").Range("F3")
.FormulaR1C1 = "=TODAY()"
.NumberFormat = "d-mmm-yy"
End With
With Sheets("Sheet3").Range("D3")
.FormulaR1C1 = "=TODAY()"
.NumberFormat = "d-mmm-yy"
End With
With Sheets("Sheet4").Range("D3")
.FormulaR1C1 = "=TODAY()"
.NumberFormat = "d-mmm-yy"
End With

End Sub
[/vba]

[Norie]

Alan

Do you want the date to be static?

[drums4monty]

Thanks xld. What to you mean by static Norie?

[Norie]

Well the code you have is putting a formula into a cell.

That formula will probably update every time you open the workbook.

So tomorrow it will read 31-Jul-2007.

[drums4monty]

I see. In this case this is exactly what I need. I get reports every Monday and they need to dated all the same. At the moment the people sending the reports either don't date them or date them the time they write the report, or even worse, copy an old report and don't change the date. This way when I rum my macro on the reports they all get dated the same.

Just so I know, how would you make it a static date?

Alan

[Bob Phillips]

[vba]

Sub DateInsert()
'
With Sheets("Sheet1").Range("D3")
.Value = Date
.NumberFormat = "d-mmm-yy"
End With
With Sheets("Sheet2").Range("F3")
.Value = Date
.NumberFormat = "d-mmm-yy"
End With
With Sheets("Sheet3").Range("D3")
.Value = Date
.NumberFormat = "d-mmm-yy"
End With
With Sheets("Sheet4").Range("D3")
.Value = Date
.NumberFormat = "d-mmm-yy"
End With

End Sub
[/vba]