How To Add A Last Saved Date In Excel

In this article, I volition show you how to auto-populate appointment in Excel when a jail cell is updated. I will show the whole process using Excel VBA. I will likewise bear witness how you lot can modify the code for your specific purposes.

Sometimes several people work on a worksheet and anyone tin can update the worksheet anytime. So, the worksheet possessor might want to run across the last updates with the update date and time.

Auto-populating dates in cells using Excel VBA (for the whole column)

Take a await at the following dynamic paradigm. You see when I put some value in a cell of cavalcade A, the next column B jail cell shows the engagement and fourth dimension I am changing the cell.

Read More: How to Combine Date and Time in One Cell in Excel (iv Methods)

You update a prison cell and the adjacent cell shows the update date and fourth dimension.

VBA Code and step by step process

            Private            Sub            Worksheet_Change(ByVal            Target            As            Range)            ' Motorcar Appointment                        Dim            Jail cell            Equally            Range            For            Each            Cell            In            Target            If            Cell.Cavalcade = Range("A:A").Column            Then            If            Cell.Value <>            ""            And then            Cells(Prison cell.Row,            "B").Value = Now            Else            Cells(Cell.Row,            "B").Value =            ""            End            If            End            If            Next            Cell            Terminate            Sub

This is the VBA lawmaking that I am going to apply. Allow'due south bear witness you where to put this code.

1) Click on the Developer tab. And then click on the Visual Basic command in the Code window.

To open Excel VBA editor go to Developer tab and then click on the Visual Bones command in the Code window.

2) Microsoft VBA code editor will open up like the following prototype. The keyboard shortcut to open this window is ALT + F11.

When you will open the VBA editor, it might bear witness blank or the existing Macro codes already available in the same workbook or from any workbook open in your Windows system.

3) Double click on the worksheet on which yous want to apply our code. So, you will notice an editor that looks like this.

To open the Sheet1 worksheet (named as VBA) related code editor, double-click on the Sheet1 (VBA) on the left side of the editor.

four) Click the drop-down part of the (General) and choose Worksheet.

Select Worksheet from the drop down (on the left side). When you will select Worksheet, all worksheet related pre-built functions volition be bachelor to you lot.

5) In the aforementioned way, from the right-side tab, choose the Change option. You will discover that ii codes are actualization in the code editor. Select and delete the lower role code.

Delete the SelectionChange related code from the editor. It is not necessary. I am suggesting to delete this part just for making the editor clean for work.

6) Now insert the code below between these lines:

Individual Sub Worksheet_Change(ByVal Target As Range)

' Insert code here

End Sub

            ' Car Date                        Dim            Jail cell            As            Range            For            Each            Prison cell            In            Target            If            Cell.Cavalcade = Range("A:A").Column            Then            If            Cell.Value <>            ""            So            Cells(Cell.Row,            "B").Value = At present            Else            Cells(Cell.Row,            "B").Value =            ""            Terminate            If            End            If            Next            Cell

When Worksheet is selected, Change is selected, input the above code in the part area.

seven) We are washed. Now save your workbook pressing CTRL + S or from the File tab. Y'all volition come across the following Microsoft bulletin. This message volition show if your workbook is saved with .xlsx extension instead of .xlsm extension. Your workbook now has VBA lawmaking, and so it cannot be saved with .xlsx extension.

Select No as we want to relieve the workbook as a macro-enabled file type.

You cannot save a workbook with macro codes with .xlsx extension.

eight) Cull Excel Macro-Enabled Workbook (.xlsm) option from the Save equally type carte and salve the file newly.

Choose the Excel Macro-Enabled Workbook (.xlsm) from the options.

Then, this is the whole procedure to auto-populate a jail cell when a change occurs in the adjacent jail cell.

Now the large question. How volition you apply this lawmaking for your specific purpose?

Say you lot want to utilize this code for another worksheet and for specific cell ranges. Similar the image below.

You want to use this lawmaking for VBA2 worksheet and for the columns C and D.

We will now modify the higher up lawmaking for dissimilar cell ranges.

Here are the steps:

1) Open the Visual Bones editor and you might find that already the previous code is showing while the previous worksheet is selected.

Excel VBA editor will show the existing codes and related worksheet when you will open once again the VBA editor.

2) Double click on the worksheet on which you want to apply your VBA lawmaking. On the right side, choose Worksheet on the left and on the right cull the Change option from the drib-down.

To select a code editor for a specific worksheet, you have to double-click on it.

3) Now use the following lawmaking:

            ' Auto Date                        Dim            Prison cell            Every bit            Range            For            Each            Cell            In            Target            If            Cell.Column = Range("C:C").Cavalcade            Then            If            Prison cell.Value <>            ""            Then            Cells(Prison cell.Row,            "D").Value = Now            Else            Cells(Cell.Row,            "D").Value =            ""            End            If            Terminate            If            Next            Cell

I am showing the places where y'all volition make the changes.

See where I am making the changes: C:C and D.

Automobile-populating dates in some specific cells (not the unabridged column)

Look at the post-obit spreadsheet. Say, in this worksheet, people tin only update data on cell range D3: D22, and adjacent cells on column E will record the appointment of terminal changes.

This time we are going to show how to auto-populate dates of some specific cells (D3: E22), not the entire cavalcade.

1) Use the following lawmaking:

            Private            Sub            Worksheet_Change(ByVal            Target            As            Excel.Range)            With            Target            If            .Count > 1            And so            Exit            Sub            If            Not            Intersect(Range("D3:D22"), .Cells)            Is            Nil            Then            Awarding.EnableEvents =            False            If            IsEmpty(.Value)            Then            .Offset(0, 1).ClearContents            Else            With            .Offset(0, 1) 					.NumberFormat =            "dd mmm yyyy hh:mm:ss"            .Value = Now            End            With            End            If            Application.EnableEvents =            True            Stop            If            Terminate            With            End            Sub

Input this lawmaking and and then see the magic.

2) Here is the result

Looks good.

This code will impact but the cell range D3: E22. Non whatever cell across this range.

If your programmer tab is not showing

i) At showtime, check out whether your worksheet is showing the Programmer tab in the tab listing.

No developer tab is showing in the following paradigm.

ii) Click on the File tab.

iii) At present choose Options from the left side menu.

iv) Excel Options dialog box will announced. Choose Customize Ribbon and and so on the right side of the dialog box, you come across the Developer choice is unchecked.

5) Check the box and click OK on the dialog box.

6) You meet, now the Developer tab is showing in the tab list.

Download Working File

So, these are 2 pieces of code that you tin can use for your specific purposes. Do you know whatever better mode to car-populate dates in Excel when cells are updated? Let me know in the comment box.

Farther Readings

How to Insert Date in Excel (7 Uncomplicated Methods)
How to Modify Dates Automatically Using Formula in Excel
Excel Automatically Enter Engagement When Information Entered (7 Easy Methods)
How to Insert Dates in Excel Automatically (iii Simple Tricks)