×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

MS Excel VBA - Append updated data from one worksheet to another worksheet and date/time stamp

MS Excel VBA - Append updated data from one worksheet to another worksheet and date/time stamp

MS Excel VBA - Append updated data from one worksheet to another worksheet and date/time stamp

(OP)


Objective: Populate the last column, Col 18, on worksheet "ws3" with the date that the updated record(s) was/were appended to worksheet, ws3


I am using MS Excel 2016 and have data within two worksheets - "ws1" and "ws3."

ws1 is refreshed (from a CSV file on the network drive) upon opening the workbook and displays the updated data with the "AccountNo." field in column A.

ws3 contains the same columns and an additonal column that I added that is named "DateThatRecordWasAppended."

There are 17 columns of data on worksheet ws1. However, for worksheet ws3, I have 18 columns.

The code below successfully appends the updated data from ws1 to ws3 - populating columns 1 through 17.


But, I am not able to populate column 18 on worksheet "ws3" with the date and time that the record was appended.


Currently reviewing but thought that some immediate insight may be necessary due to spending over an hour to resolve.

Any insight as to the needed modifications to accomplish objective is appreciated.

Conceptualizing a bit - maybe the code to actually populate the 18th column should be after all of the columns have
been copied for a particular row.

Or, maybe a nested FOR statement whereby the data is copied by row initially...

The review continues...

CODE

Sub TestAppend()

    Dim c As Range, f As Range
    Dim ws1, ws3

    Set ws1 = Worksheets("Data_1")
    Set ws3 = Worksheets("Data_3")
            
    For Each c In ws1.Range(ws1.Range("A1"), ws1.Cells(Rows.Count, 1).End(xlUp)).Cells

        Set f = ws3.Range(ws3.Range("A1"), _
                           ws3.Cells(Rows.Count, 1).End(xlUp)).Find( _
                                      What:=c.Value, lookat:=xlWhole)

        If f Is Nothing Then
            ws3.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Resize(1, 17).Value = _
                                                        c.Resize(1, 17).Value
            'ws3.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Resize(1, 18).Value = Now()  'Iter 2 - Error
                                   
        End If

    Next c
   
End Sub 

RE: MS Excel VBA - Append updated data from one worksheet to another worksheet and date/time stamp

Hi,

Where is the cell containing the date in question?

Or is it assumed that the date is the current date because this process will take place immediately after the import occurs?

What is variable f supposed to be doing?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: MS Excel VBA - Append updated data from one worksheet to another worksheet and date/time stamp

Here’s a possible solution.

On the sheet containing your IMPORT,
1) add a column for your datestamp
2) enter =NOW() in row 2 of that column
3) right-click in your querytable and select Data Range Properties
4) in External Data Range Properties CHECK Fill down formulas in columns adjacent to data

Now you have the date time of the import on every row of the import.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: MS Excel VBA - Append updated data from one worksheet to another worksheet and date/time stamp

(OP)
Thanks for the insight!

Yes, the date is assumed to be the current date - date that the records were appended

Will test the suggestion.

The values within the column "DateThatRecordWasAppended" on the worksheet named "Data_3" (that is assigned the variable ws3)
will be a audit trail whereby any previous values entered in this column should not change as new records are appended.

Also, the workbook may not be opened on a daily basis.

The variable "f" is used to compare the Account Numbers between the two worksheets, Data_1 and Data_3. For example, if there is
a record with account number 2456 that now appears on worksheet "Data_1", the code will not allow that record to be appended to worksheet "Data_3" if there is a record within the worksheet "Data_3" that have account number 2456.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close