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

[BxWill]

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

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.

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

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...

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

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[BxWill]

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.