Date Stamp within Excel table - Changes in multiple rows and columns and new rows added

Hi all,

I am totally new to this forum posting thing, but have been trolling all forums I can find on Google and I just cannot find a combination of answers to get me to what I need. I am also very new to using Macros and VBA so I have been struggling to get to the bottom of this one, but I feel its definitely achievable with the right set of code (hopefully simple enough for me to understand)

Basically I am trying to achieve the following date and time stamps in a set of three columns based on the following:

1. [Column1] of Table1 = when this is updated then the date and time is stamped in [Column5] of the excel table.a. my issue here is that when I paste data into this column onto multiple rows at the same time, only the first row is updatedwith the date - I need it to be every row not just the first row.

2. A range of columns, say [Column2]:[Column4] in the excel table = when this range of columns within the excel table is updated, whether it be a single cell, or a combination of cells at the same time via copy paste, I want the corresponding row to be updated with the date and time stamp in [Column6] for each updated row.

3. And finally, whenever a new row is added to the table, I would like the date and time stamp to be added to the new row in column [Column7]. So in theory every row will have a date in [Column7] but shouldn't change, only update when added in the first instance.

I started with the below very basic Change event code and I could get it to work on some of my requirements above but not all (especially the new row added and also the copy and paste into a range)


Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 1 Then
Application.=EnableEvents = False
Cells(Target.Row, 2).Value = Date + Time
Application.EnableEvents = True

End If

Emd Sub

Thanks mehmetcik, appreciate your feedback.

I had a go at it and I couldn't get either of the four codes you provided to work? Also, just wanted to clarify i am looking for this code to work only in a excel Table range. I was expecting to see references to "Table1[Column1]" etc.

All i did was copy and paste the code into the sheet1 VBA code section - am i potentially doing something wrong or maybe the code needs a slight tweak? what are your thoughts?

Originally Posted by mehmetcik

Part One:-

Please Login or Register  to view this content.

Part 2:-

Please Login or Register  to view this content.

Part3:

Please Login or Register  to view this content.

So putting those together and tidying up:-

Please Login or Register  to view this content.

Thanks mehmetcik, appreciate your feedback.

I had a go at it and I couldn't get either of the four codes you provided to work? Also, just wanted to clarify i am looking for this code to work only in a excel Table range. I was expecting to see references to "Table1[Column1]" etc.

All i did was copy and paste the code into the sheet1 VBA code section - am i potentially doing something wrong or maybe the code needs a slight tweak? what are your thoughts?

Unless I'm missing something blatantly obvious, part 3 of the question is pointless / redundant.

Effectively, to add new rows to the table, you would be adding data somewhere in columns 1-4, which would be timestamped as in part 1 or part 2, meaning that any timestamps added in part 3 would only be on otherwise empty rows

thats very true, but i am trying to maintain the date the data was first added to the range. if subsequent changes are made then the date will update, so by having part 3 based on when the row was added, it means any other changes wont affect the timestamp for that particular column.

Originally Posted by mehmetcik

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachements to open the upload window.

View Pic

i have added an example workbook to illustrate what i am wanting to see - let me know if any questions and thank you for taking the time to help me out, i really appreciate it.

So effectively, if it's a new row (i.e. if column 7 is blank) add the timestamp to column 5 or 6 as appropriate, and to column 7. If column 7 already has a timestamp, just change column 5 or 6 as appropriate?

It looks like you might have uploaded a sample file to the forum, but not attached it to the thread, so we can't see it.

You might have to upload it again and attach it to the thread, if I remember correctly, they are automatically deleted after 10 minutes if they haven't been attached to a thread.

Let me know if you can see this one

Originally Posted by jason.b75

So effectively, if it's a new row (i.e. if column 7 is blank) add the timestamp to column 5 or 6 as appropriate, and to column 7. If column 7 already has a timestamp, just change column 5 or 6 as appropriate?

It looks like you might have uploaded a sample file to the forum, but not attached it to the thread, so we can't see it.

You might have to upload it again and attach it to the thread, if I remember correctly, they are automatically deleted after 10 minutes if they haven't been attached to a thread.

Hi jason,
Ive added he attachment now, apologies for confusion.
Take a look and let me know, i tried to put in an example of what i am trying to achieve to illustrate.

Hi jason.b75 & mehmetcik,

I was wondering if you had any thoughts on the example attachment I provided.
Understand you're probably busy but just wanted to check in and see if you can help me?

As always, thanks in advance for your help.

jarheadmctavish

This looks like it should be simple to solve, but it's making my brain hurt (that doesn't take much though)

Give this a try, I don't think it does everything you asked for but it's a starting block for you to tinker with.

Please Login or Register  to view this content.

To create the sample code, I've set 2 sections that you can copy and edit as needed, the red section will timestamp column 10 when column 2 is changed, the green section will stamp column 11 when column 3 is changed. Note that the column name after 'Case' is case sensitive, so "Column2" is not the same as "column2"

For simplicity, if you copy data and paste to multiple columns, the timestamp will be based on the first (leftmost) column.

There is another line after that, which should stamp column13 when a new row is inserted.

See how it goes, I'm not expecting this to be perfect first time, if it then that's more luck than judgement.

jason.b75,
Thanks so much mate, its a very good start and i can start to understand the code as well.
thanks again, ill keep you posted on any changes i make and how i get on.

thanks again.