+ Reply to Thread
Results 1 to 14 of 14

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

  1. #1
    Registered User
    Join Date
    03-04-2016
    Location
    Edinburgh, Scotland
    MS-Off Ver
    Office 2013
    Posts
    9

    Lightbulb 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

  2. #2
    Forum Guru
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,450

    Re: Date Stamp within Excel table - Changes in multiple rows and columns and new rows adde

    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.
    Last edited by mehmetcik; 03-04-2016 at 06:48 PM.
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  3. #3
    Registered User
    Join Date
    03-04-2016
    Location
    Edinburgh, Scotland
    MS-Off Ver
    Office 2013
    Posts
    9

    Re: Date Stamp within Excel table - Changes in multiple rows and columns and new rows adde

    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?

  4. #4
    Registered User
    Join Date
    03-04-2016
    Location
    Edinburgh, Scotland
    MS-Off Ver
    Office 2013
    Posts
    9

    Re: Date Stamp within Excel table - Changes in multiple rows and columns and new rows adde

    Quote Originally Posted by mehmetcik View Post
    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?

  5. #5
    Forum Guru
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,450

    Re: Date Stamp within Excel table - Changes in multiple rows and columns and new rows adde

    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

  6. #6
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Date Stamp within Excel table - Changes in multiple rows and columns and new rows adde

    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

  7. #7
    Registered User
    Join Date
    03-04-2016
    Location
    Edinburgh, Scotland
    MS-Off Ver
    Office 2013
    Posts
    9

    Re: Date Stamp within Excel table - Changes in multiple rows and columns and new rows adde

    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.

  8. #8
    Registered User
    Join Date
    03-04-2016
    Location
    Edinburgh, Scotland
    MS-Off Ver
    Office 2013
    Posts
    9

    Re: Date Stamp within Excel table - Changes in multiple rows and columns and new rows adde

    Quote Originally Posted by mehmetcik View Post
    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.

  9. #9
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Date Stamp within Excel table - Changes in multiple rows and columns and new rows adde

    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.

  10. #10
    Registered User
    Join Date
    03-04-2016
    Location
    Edinburgh, Scotland
    MS-Off Ver
    Office 2013
    Posts
    9

    Re: Date Stamp within Excel table - Changes in multiple rows and columns and new rows adde

    Let me know if you can see this one
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    03-04-2016
    Location
    Edinburgh, Scotland
    MS-Off Ver
    Office 2013
    Posts
    9

    Re: Date Stamp within Excel table - Changes in multiple rows and columns and new rows adde

    Quote Originally Posted by jason.b75 View Post
    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.

  12. #12
    Registered User
    Join Date
    03-04-2016
    Location
    Edinburgh, Scotland
    MS-Off Ver
    Office 2013
    Posts
    9

    Re: Date Stamp within Excel table - Changes in multiple rows and columns and new rows adde

    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

  13. #13
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Date Stamp within Excel table - Changes in multiple rows and columns and new rows adde

    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.

  14. #14
    Registered User
    Join Date
    03-04-2016
    Location
    Edinburgh, Scotland
    MS-Off Ver
    Office 2013
    Posts
    9

    Re: Date Stamp within Excel table - Changes in multiple rows and columns and new rows adde

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Insert rows in linked table that are added to secondary table
    By Ida LaValley in forum Excel General
    Replies: 4
    Last Post: 11-16-2020, 12:00 PM
  2. Replies: 5
    Last Post: 04-21-2014, 12:14 AM
  3. Replies: 3
    Last Post: 02-17-2014, 09:20 PM
  4. Replies: 1
    Last Post: 11-29-2012, 06:24 PM
  5. Replies: 2
    Last Post: 11-03-2009, 03:30 PM
  6. Get data from duplicate rows into added columns
    By Lithium in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-08-2009, 02:19 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1