Help extract value from a cell with vba

[nick1bn]

Dear All,

First, i have just little (very little) knowledge about VBA. In my workshit i have a column with numerical data, i.e. profit or loss on a futures investments, that automatically update every morning. I would like to insert a button (or whatever works) in order to extract that data when i close the position (e.g. a button with "take profit" or "close position") and copy that value in another cell. The problem is that the day after the cell containing the profit/loss on the position will update with the new price, but i need that the data extract before doesn't change. Can someone please help me?

 

[Brianwarnock]

I am sure that there is more to this than meets the eye

to make one cell equal to another is simply
Range("B1") = Range("A1")

Assign the maco this is coded in to a form button

Brian

 

[smiler44]

I think it is important we need to know how the sheet updates automatically each morning.

in the meantime create a new work book to practice with and try the following

go to the developer tab, on the left click visual basic.
in the window that opens click or double click (I'm not sure) the sheet that you want to update.
a code window should open, on the left will be General, on the right Declarations. click the down arrow by General and select Work sheet, click the down arrow by Declarations and select Activate
in the code window you should see
private sub worksheet_ activate ()

end sub

in between these two lines try this code
newsheet.Range("B1") = oldsheet.Range("A1")


change newsheet to the sheet name that you want the date to go to, change B1 to the cell that you want the data to go into. change oldsheet to the name of the sheet you want to get the data from and change A1 to the cell where you want the data to come from .

I think that when you click the sheet "newsheet" the code will run and update the newsheet where you want the data to go to from the oldsheet

smiler44