Enter Date

[tracktor]

When I press a control button I want a macro to run a routine and enter todays date (and time) in column AI at the end of each row that doesn't already have the date in it. I am using the =Now() formula with the following routine. The date is static once it is entered at the end of the row, because I copy and paste just the values.

Sub Date ()
'Copy the =Now()formula
Range("AI1").Select
Selection.Copy
'Select a column with data in it to find the last row
Range("G3").Select
Selection.End(xlDown).Select
'Move the active cell over 28 cells and up 1 to get to the bottom of column AI
Selection.Offset(-1, 28).Select
'Select the empty cells at the bottom of the column minus the header
Range(Selection, Selection.End(xlUp)).Select
Selection.Offset(1, 0).Select
'Paste the formula to the empty cells
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

It works but I know there is an easier way that isn't so cumbersome.

Thanks in advance

 

[Ruddles]

I can't follow what your code is doing at all but I would have done it like this:-

Option Explicit
 
Public Sub InsertDates()
 
  Dim iLast As Long
  Dim iRow As Long
  
  With ThisWorkbook.Sheets("Sheet1")
    iLast = .Cells(Rows.Count, 7).End(xlUp).Row
    For iRow = 1 To iLast
      If IsEmpty(.Range("AI" & iRow)) Then
        .Range("AI" & iRow) = Format(Now(), "dd-mmm-yyyy hh:nn:ss")
      End If
    Next iRow
  End With
 
End Sub

 

[tracktor]

Thanks a bunch! Works great!

 

[Ruddles]

There's seldom any need to use .Select to move around the workbook if yoiu can address ranges explicitly.

 

[tracktor]

This code only fills in the date in the last cell in the column at the end of the data. I import anywhere from 100 to several thousand records at a time. I need the date on each row with each record.

 

[tracktor]

Never mind, my bad, I don't know what I did, but it works right.

 

[tracktor]

I cant seem to get this to work within another routine that I am trying to nestle it in. It works fine on it's own. I don't know much about coding other than copy and paste.

 

[tracktor]

I figured it out. I had to Call it rather then put within my code.

 

[Ruddles]

I would have replied to any of those posts but I couldn't get a word in edgeways!

Glad you're sorted now though.

 

[tracktor]

LOL, Sorry! Thanks for all your help.