Putting Date In Last cell

[Dazzawm]

When I close a worksheet I would like todays date automatically put in the last cell in column H. It cant be after the last used cell as it will add it on the end each time whereas I need it overwritten each time. Also the code will be used on 20 different files which have different ranges in each file. Would I have to put it in each file or will the PWB do it? Thanks.

 

[VoG]

Try this in the ThisWorkbook module of each workbook. Change Sheet1 to suit.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("Sheet1").Range("H" & Rows.Count).End(xlUp).Value = Date
End Sub

 

[Dazzawm]

Will that not add the date each time? For example it will add 01/07/11 in cell H500 then next day I close it it will add 02/07/11 to cell H501 and so on? I need it to overwrite each time.

 

[VoG]

That will overwrite. Be sure to enter something in H500 or whatever first otherwise the first time it runs it will overwrite whatever is in the current last cell.

 

[Dazzawm]

Thanks that works but is there anyway I can get away from having to save the files as macro-enabled workbooks by somehow putting the code in my PWB?

 

[VoG]

You can try but I don't think it would work in the PWB. You would need to amend the code like this (also I've noticed an oversight)

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ActiveWorkbook.Sheets("Sheet1").Range("H" & Rows.Count).End(xlUp).Value = Date
ActiveWorkBook.Save
End Sub

If it did work (which I doubt) the code would run when you closed any workbook.

 

[Dazzawm]

Also I have recorded this macro to run before close but what if the rows vary each time? At the moment there are 5238 but I may add rows from time to time and basically the code counts the amount of rows and puts the figure in column A

Sub Macro1()
Range ("A2:A3").Select
Selection.AutoFill Destination:=Range("A2:A5238")
Range ("A2:A5238").Select
Range("A2").Select
End Sub

 

[VoG]

Try this

Sub Macro1()
Dim LR As Long
LR = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
Range("A2:A3").AutoFill Destination:=Range("A2:A" & LR)
End Sub