Date/Timestamp after pasting table not auto updating

[wrigh333]

My ERP spits out a file with the table on it, then I copy the columns and paste into my existing excel file where I use the values from the table. Table is A1:J95, trying to record a timestamp at L2

I've tried a couple solutions, but they all fail to update when the whole table is pasted and I'm puzzled.
However, If I copy and paste individual cells, all methods work. Even if I copy A2 and paste into A2, the timestamp will update for all methods.
I've tried the circular reference method and a VBA code pasted below.

Background about this workflow:
The entire table gets pasted over
I only need 1 timestamp to record when this overwrite takes place
Columns A-C values won't change, the rest of the columns are inventory counts which may or may not change.
The rows are different part numbers

Please Login or Register  to view this content.

[wrigh333]

Does anyone have any insight?

[GlennUK]

You say the entire table gets pasted over? How ... manually?

[wrigh333]

Yes, I do it manually.

File "A" gets generated with the table A1:J95
I highlight the columns from A-J and copy.

Then I go to File "B" and manually paste the table on cell A1 and it overwrites everything that was there on A1:J95.

That VBA code is supposed to put a date/timestamp on column L if column A is updated. For example, if A2 is overwritten, L2 will have a date/timestamp for that overwrite. If A15 is overwritten, L15 will have a date/timestamp for that overwrite.
However, when I copy and paste the whole table, it doesn't update the date/timestamp.
The VBA code does work though, If I just copy A2 and paste into A2, then it will update the timestamp on L2.

For some reason when I copy and paste the whole table, A2 doesn't think that it was overwritten and it doesn't update the date/timestamp.

[GlennUK]

I did a very quick test ... when you copy and paste a range it only updates the timestamp on the first row pasted. In fact, looking at the code, there is nothing in there to test and process for Target being a range instead of a cell.

[ChemistB]

It would help if you uploaded an example spreadsheet showing what and where you are copying and pasting so we can better understand what you expect to happen.

[wrigh333]

Files attached and simplified for discussion purpose.

At the end of the day I only need 1 date/timestamp when I paste the whole table to file B.
I picked up the VBA code somewhere online and it sort of works, but not completely.

The code is fairly simple, if anything in column A is overwritten, then it puts a timestamp in column L when that happens.
It just doesn't update the timestamp if you paste the whole table which is how I use that sheet in File B.

[wrigh333]

I was able to get it to work using this VBA code.
Now when I paste in the entire table, it gives a timestamp in L1.
It doesn't care if anything changed, just the fact that it is overwritten changes the timestamp which is good for me.

Please Login or Register  to view this content.