CODEWITHSUNDEEP
excelvba
G M
G M

Reputation: 131

How can I get today's date in an Excel sheet?

I am trying to add today's date to column A in sheet2. Each time my macro pastes my selection in sheet1, column B, to a new row in sheet2.

My script works to add the new row and paste my selection in sheet2, but I can't figure out how I should get the date in column A for the new row in sheet2. Here is the script in my macro;

Sub move()  
    Dim i As Integer 
    Application.ScreenUpdating = False
    ActiveWorkbook.Sheets("Sheet1").Range("A1,A2,A3,A4,A5").Copy
      
    Sheets("Sheet2").Select
    i = 3
    While Range("B" & i).Value <> ""
        i = i + 1
    Wend
    Range("B" & i).Select
   
    Selection.PasteSpecial (xlValues), Transpose:=True
 
    Worksheets("Sheet1").Range("A1:A5").Clear
 
End Sub

Upvotes: 13

Views: 168372

Answers (2)

ARich
ARich

Reputation: 3279

Try the Date function. It will give you today's date in a MM/DD/YYYY format. If you're looking for today's date in the MM-DD-YYYY format try Date$. Now() also includes the current time (which you might not need). It all depends on what you need. :)

Upvotes: 13

David Zemens
David Zemens

Reputation: 53623

Here's an example that puts the Now() value in column A. 

Sub move()
    Dim i As Integer
    Dim sh1 As Worksheet
    Dim sh2 As Worksheet
    Dim nextRow As Long
    Dim copyRange As Range
    Dim destRange As Range

    Application.ScreenUpdating = False

        Set sh1 = ActiveWorkbook.Worksheets("Sheet1")
        Set sh2 = ActiveWorkbook.Worksheets("Sheet2")
        Set copyRange = sh1.Range("A1:A5")

        i = Application.WorksheetFunction.CountA(sh2.Range("B:B")) + 4

        Set destRange = sh2.Range("B" & i)

        destRange.Resize(1, copyRange.Rows.Count).Value = Application.Transpose(copyRange.Value)
        destRange.Offset(0, -1).Value = Format(Now(), "MMM-DD-YYYY")

        copyRange.Clear

    Application.ScreenUpdating = True

End Sub

There are better ways of getting the last row in column B than using a While loop, plenty of examples around here. Some are better than others but depend on what you're doing and what your worksheet structure looks like. I used one here which assumes that column B is ALL empty except the rows/records you're moving. If that's not the case, or if B1:B3 have some values in them, you'd need to modify or use another method. Or you could just use your loop, but I'd search for alternatives :)

Upvotes: 5

Related Questions