Copying values to 2nd sheet based on specific changing circumstances.

[markDuffy]

Hi Guys
Copying values to 2nd sheet based on specific circumstances.
I have an issue I would like to resolve. I want get some values from one worksheet to another under certain conditions. I don’t know what title to give it so that it can be referred to by others so please if you can help with a better title that would also be great.
My example has conditional formatting applied using if the value of D is greater than B background redish.
I want to write a macro which will look in column “B1” of “What I have” and enter its value into “A2” in sheet “What I want”.
Then I would like to go down to the last row in column “E”. When the value changes from hold to sell then the value in “A”, date and the value in “D” are entered in entered into sheet “What I Want “ under column “B and C”. When the value changes to Sell to Hold I want the values in “B” to go to “D” in “What I want” and the value in “A” in “what I have” to “G” in ” What I want”.
As you can see I have other calculations which I will then add to go to the last row in “What I want”
In will have up to 100 stocks in sheet What I have and will next wan the same to move to next and repeat the operation continuing to do so until there are no more.

Thanks Mark

[ARowbot]

This currently looks at the value in "E" and then copies the appropriate cells into the next available row in the other sheet. See if that is ok for you and then can you just clarify how else you would like it to work please.

Please Login or Register  to view this content.

[sktneer]

Try this.....

Please Login or Register  to view this content.

Please find the attached workbook and click on the button on What I Want sheet to get the desired output.

[markDuffy]

Hi To you both.

I am over the moon. It took me longer to work out what I needed than it has for you both to work out your approaches. For my particular requirements Sktneer's better fits my needs which are to be able to see the changes in as few lines as possible. It is a pleasure to see the numbers appear.

Sktneer. The numbers you have given me are precisely what I was looking for. The other calculations, Sum, percentage and days are done on "What I want". However the dates and the name of the stock come from "What I have". Would it be possible to include these so that they were drawn from "What I have" as they are directly related to the numbers you have so far. Obviously they will change as the stock changes. I can easily pop in the stock name but you may have a cleaner solution.

Other than that I can't tell you how pleased I am. I now have to examine your work to see how much I can learn from reading it and a lot of it are combinations I have not see before. A lot to learn.

Thank you
Mark

[sktneer]

Hi Mark,

Not sure about the Stock Names in col. A of What I Want sheet, couldn't get the logic, but for Dates in col. B, you may try the below code......

Please Login or Register  to view this content.

Does this help?

[markDuffy]

File uploaded

[markDuffy]

Hi Stkner
It is terribly embarrassing that I am not able to take the code you have written and manipulate it so as to achieve what I need. I understand that for you to understand my logic is all important.
I am looking at specific stocks which have performed well and I want to be able to check on a daily basis what would have happened if I had bought them and then sold them if they had dropped a given percentage - 10% and 5% in this example. My logic being that a good company continues to be so.

I have a system which can download stock details onto which I am bolting my stuff as you will see.
I will have maybe up to 50 of these to look at which I will download though testing the system I have downloaded more
.
I am going to upload this workbook so you will see precisely what I am doing. I have replaced the sheet names with high Price and Summary.

Seeing it all in context will show you why I need to see the dates and establish how long I have held a particular stock. I will have in place a box system which moves up with the price and automatically sells at my agreed percentage below. It may be that the stock goes down to my sell point without ever going up. If so I lose.

I then want to establish what would have been the most applicable percent to use.

I hope that I have made this clear enough. I cannot express how thrilled that you are helping me with your expertise.

All the Best Mark
It’s a beautiful sunny day in London. Spring has arrived.

[sktneer]

Hi Mark,

Right now I am busy in a project but I will look at it as soon as I get some free time.

[markDuffy]

Hi Sktneer
You are a star. I really hope it interests you.
Thank you.
Mark

[sktneer]

Hi Mark,

This is the max help I can offer you. Try below code....

Please Login or Register  to view this content.

[markDuffy]

Hi Sktneer.
This is utterly fantastic. I will be up all night and likely all this week trying to work out how it all works. I can't thank you enough. I still want to get the other dates in but I am sure I can figure it out. I hope this is useful to those that follow me and they will see the beautiful simplicity of this. I never expected to get all the stocks done and did not dare ask.
All the best
Mark

[sktneer]

You're welcome Mark! Glad I could help.
And thanks for the feedback as well.

[markDuffy]

Hi Sktneer
I have worked at the code you have sent me with view to getting the additional dates into column G. I have not succeeded. I absolutely must have then as they are needed so I can have the days which is critical for me.

I feel obliged to ask you first as you might feel irritated if went ahead and put my request out to Excel Forum without asking you.

Mark

[sktneer]

Hi Mark,

Please find the attached workbook and see if you get the other dates correctly.

[sktneer]

Hi Mark,

You visited the forum but didn't respond to the post#14 and that's quite a strange behavior.

Anyways I have modified the code again, have a look at the workbook (if you have time ).
Only few dates are missing in col. G for the stocks where column ends with Hold so didn't know which dates were to be placed in that case.

[markDuffy]

Ah I think I had it open with two copies and not refreshed. I am totally in your debt. This time i will do even more study comparing the two files. Thanks