How to do auto sorting

Originally Posted by MarvinP

Hi calvk81 and welcome to the forum.

If you are talking about sheet1, you need to remove the blank column between Daily Open and Current Price. That way Excel will know all the rows stay together when you autosort.

See http://www.easyexceltutorial.com/excel-tables.html for a good example.

hai marvinp
thanks for your reply. Sorry if i dont write it correctly. Actually sheet2 is the place i need to use Autosort. As you could see from sheet2, there is 14 currency pair. And This is fix. And the value beside the currency pair will always changing. And i need the currency pair with the highest to smallest value. So that is the positive will be above and negative will stay below. And those pair will be always moving depending on the value in sheet1. That is the sheet i will entered my data manually.

Originally Posted by MarvinP

Hi Calvk81,

Look at the attached that will sort the pivot table when things in column H change. Look behind sheet1 at the event macro that does the refresh.

marvinp
thanks for your quick response. Actually the sort order i would like this.
when i change the value in column H. The value in column D will auto sort it in descending way. So, if next time i change the values in H17 44 to 10. The GBPUSD will move to step3 and EURUSD will move to step1 and USDCHF will move to step2 and so on.

marvinp
thanks for all the work you have done.That i have to do it manually. right now, when i change the column h to receive the values from my MT4 software. It will not update automatically. Is there a way to make it auto refresh every 10 seconds?

Originally Posted by MarvinP

Hi,

Did you see the code behind sheet1? It will only trigger if you change one of the cells between G16 and H20. If you are entering values below that in column H then change the 20 to the last row you will enter data into.

Other than that - if you are already running VBA code to pull in data, simply add a line of code after it pulls stuff in of

Please Login or Register  to view this content.

and that will update the Pivot Table.

what if the range h16:h20 (this is the main data, wont be adding anymore data) remain unchange? Just only the value inside it will change every 10 seconds? Exp. The value of EurUsd is 1.2345 right now, the next second it change to 1.2350. But the value in pivot table will not change to 1.2350 unless i press the refresh on excel.

Originally Posted by MarvinP

The way the event macro works is to fire when data in that range changes. I guess your software doesn't trigger a change event. SO - add a line in your software to the above so you won't need to press the refresh.

Without having or using your other software that updates your data, I really can't test my answer.

Marvinp
do u really could help me to test on the software. If you busy, that wont be a matter. The software is really actually a trading software called metatrader 4. It can freely download on anywhere on the net. That is the software that i receiving the values from.

Originally Posted by MarvinP

Hi calvk

I have no desire to download and install metatrader 4. Sorry. Perhaps some other forum member will want to help with this.

Ok marvinp.
thanks for your help. Just only one last question. If i put the pivot table on sheet4. Could i do a macro to refresh the sheet4 on every 30 seconds? Cause when i press the refresh button under the pivot tools - options - refresh. And the values will refresh. If i could automate this process, then i think everything is settle. Thanks for your help till now.

Best way around it would be to write your own sorting algorithm, then apply the resultant sort order to all the linked sheets. ie. Don't use the Excel auto sort.





_____________________
POS System

marvinp
i just find a macro that will do refresh when the values update. but i cpu is running on heavy load.

Please Login or Register  to view this content.

could you help me to add some time interval like it will refresh at 1minute.

thanks

Originally Posted by toddgold

Best way around it would be to write your own sorting algorithm, then apply the resultant sort order to all the linked sheets. ie. Don't use the Excel auto sort.

toddgold
thanks for your reply. Actually i am a total noob in excel programming. i also hope to learn how i could do the trick.