PDA

View Full Version : Putting A Date Into A Cell



drums4monty
07-28-2007, 06:08 AM
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
07-28-2007, 06:36 AM
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
07-30-2007, 12:10 AM
Why not post your solution - it could help someone else in the future!

drums4monty
07-30-2007, 11:15 AM
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
07-30-2007, 11:50 AM
You can tidy that up



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

Norie
07-30-2007, 11:55 AM
Alan

Do you want the date to be static?

drums4monty
07-30-2007, 01:18 PM
Thanks xld. What to you mean by static Norie?

Norie
07-30-2007, 01:21 PM
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
07-30-2007, 01:31 PM
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
07-30-2007, 03:09 PM
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