Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Automatically Timestamp Data Entries in row instead of column

0

I refer to the video 'Automatically Timestamp Data Entries in Excel'. The 'Input Time' and 'Updated Time' are entered in column downwards. Can I arrange to make them appear in rows?

Let's say, my data Range is A3:Z100. Can I arrange in such a way that when I enter fresh data in column B's range, cell B1 will show The 'Updated Time', and cell B2 will show the 'Input Time'. And this can go on all the way to column Z.

Thank you in advance.

Answer
Discuss

Answers

0
Selected Answer

Hi Victor.

Instead of using the Range property in lines like this:

Set myDateTimeRange = Range("E" & Target.Row)

you can (set your target range and) instead use the Worksheet.Cells property to read like this (changes in bold in this extract):

Private Sub Worksheet_Change(ByVal Target As Range)
'Timestamp Data
'   TeachExcel.com

Dim myTableRange As Range
Dim myDateTimeRange As Range
Dim myUpdatedRange As Range

'Your data table range
Set myTableRange = Range("A3:Z100")

'Check if the changed cell is in the data tabe or not.
If Intersect(Target, myTableRange) Is Nothing Then Exit Sub

'Stop events from running
Application.EnableEvents = False

'Set row for the Input date/time
Set myDateTimeRange = Cells(2, Target.Column)
'Row for last updated date/time
Set myUpdatedRange = Cells(1, Target.Column)

where Cells(x,y) has two numerical arguments; x= the row (1 and above) and y= the column (1 and above). E.g Cells(1, 2) means the cell in the first row and the second column i.e. B1.

The attached file contains those changes.

Hope this helps.

Discuss

Discussion

Thank you for John Ru who answered my earlier question. May I add a further requirement for that same worksheet: Let’s say row 1 to row 12 is pink zone (the range) with the timestamp cells in row 1 and row 2. Can I make row 14 to row 25 into a yellow zone range where I make row 14 and row 15 cells its respective timestamp cells?  Can I also make row 27 to row 39 into blue zone and make row 27 and row 28 cells its respective timestamp cells?   Is it possible to set that in this same one worksheet, when data is entered into the yellow zone range, only the yellow zone timestamp cells are registered but not the timestamp cells of pink and blue ranges.   I have made an Excel worksheet on how this would look like, but cannot attach it to this discussion. Thankyou in advance.
Victor (rep: 8) Jul 20, '22 at 11:59 pm
Victor. You need to make a new question, explaining your additional need and attaching your new file to that. Please don't address it to me, so others might answer (and especially since I have very little free time over the next few days) 
John_Ru (rep: 3677) Jul 21, '22 at 1:16 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login