Automatically activate code VBA at change cells from EXTERN data source-without actionUser

[endy5]

Hi guys,
I became new user because I have problem.

Description:
On List1, cells (A1, B1, C1) I have input cells from extern sources. Cell A1 for extern source1, cell B1 for extern source2, cell C1 for extern source 3. Input data is changing irregurarly. Some time quickly (10-15x per 1sec), some time slowly (1-2x per 1sec). Is possible change A1 will be 2x quickly than B1 (example), sources extern data are independent of each other.

Main problem is:
I need synchronize data in time. If change whatever cells (A1 or B1 or C1) - I need capture (copy value and format) of all cells (A1, B1,C1) to other list (List2) or other sheet (I do not care) but I would like the newest data was always in first row.
Example from Sheet1 List1 A1:C1 copy to Sheet2 List1 A1:C1, before copy new data I would like to moving older data down (select A1 in Sheet2 List1 and insert new first row) then insert cells A1:C1 from Sheet1 List1. Data will be sorted descending in time.
This is easy... :-)
BUT
I need all running automatically, user will be outside PC (no move mouse, no select other cell, no enter,... user is away!)

Possible way to solution
Routine Worksheet.Change()
- I failed me activate... :-( routine requires select other cell but user will be outside PC

Routine Worksheet.Calculate()
- I inserted in Sheet1 List1 whatever cells out A1:C1 any formula,
example cell A2: "=2*A1", B2: "=2*B1" , C2: "=2*C1"
Why? When change whatever cells A1 or B1 or C1 => automatically activate Worksheet.Calculate().... GREAT! It is my first step to solution... unfortunately - when I insert new first row to Sheet2 List1 doing activate again Worksheet.Calculate()... result is loop code.
I cannīt solved my task.

Can you anybody help me, please, write code VBA for my case? My knowledge about VBA programming is poor.
If is any unclear - ask me.
Sorry my english.
Thank you very mutch all!!!
Best regard, Endy

[Jakobshavn]

You are on the correct path.

you need to structure the Calculate Event macro to avoid unnecessary looping. Say the data is being feed into Sheet1 and we want the snapshots in Sheet2. Here is a typical macro to do this:

Please Login or Register  to view this content.

[endy5]

Hi, thank you for code. I insert to excel (2003) but nothing... :-( no action. Is it ok? What do I do bad?excel1.png

[Jakobshavn]

Make sure macros are enabled!

[endy5]

:-) some problem on my pc, after restart VBA code is function BUT:
1.) when change Sheet1 cell A1 manually (cell A2: =2*A1 - activate Worksheet.Calculate() ) - code is run ok! Insert and copy ok.
2.) after connect extern data - immediate collision, see screen excel2.png
3.)I tried simulate extern source data from other xls file - see. screen excel3.png

excel responds very similarly as previous case ad 2.)

[endy5]

nobody...??? :-( please, help me with write VBA code. Thank you!