Exporting data to other columns in the same work book

[Cargodog]

Hello,

I'm not sure how to explain my question, as I'm not familiar with the Excel Lingo, but here goes.

I have created a calculator for position sizing of stocks. It's very basic. Just a few inputs and the other data are calculated - brilliant.

Now, as time passes, I'd like to keep a log of my trades. I've created the columns I need underneath the calculator, as I want to keep everything on the same page. In my log I will need most of the values that are in the calculator (both manually inserted and calculated). I could just enter them manually every time, but that seems a bit cumbersome.

Question: Can I enter all the values in my calculator, get the results and then transfer necessary data to a NEW line on my log?

I don't know if that makes, sense.

Thanks in advance...

 

[mumps]

For each trade that you enter, you enter data in the 5 white cells (E5, E7, E8, E9, J5). Which of those 5 cells would you complete last?

 

[Cargodog]

For each trade that you enter, you enter data in the 5 white cells (E5, E7, E8, E9, J5). Which of those 5 cells would you complete last?

That would be E9.

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make the entries into 5, E7, E8, J5 and E9 making sure that E9 is the last cell to be filled and exit the cell. If you need to enter data into the other columns in the log, the macro will have to be modified.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("E9")) Is Nothing Then Exit Sub
    If Range("E7").Value - Range("E8").Value > 0 Then
        Cells(Rows.Count, "C").End(xlUp).Offset(1, 0) = "LONG"
    Else
        Cells(Rows.Count, "C").End(xlUp).Offset(1, 0) = "SHORT"
    End If
    Cells(Rows.Count, "F").End(xlUp).Offset(1, 0).Resize(1, 6).Value = Array(Range("E12").Value, Range("E7").Value, Range("E8").Value, Range("E14").Value, Range("E9").Value, Range("E11").Value)
End Sub

 

[Cargodog]

WOW! That is very impressive. That works perfect for what I described to you. I wasn't actually expecting you to write the code. Had I known it was this complicated, I'd probably just have left it. Anyway. I'm glad you did.

I'm almost afraid to ask, as I don't want to see unappreciative, but...

When I complete info in E9, it exports all info correctly to line 24. Would it be possible for me to insert a "button" in the calculator? I could name it "Export" and make that the Activation cell? That way I could play around with the calculator to my hearts content, but only export the result when I was ready?

Also, how could I add a feature that adds the current Date to line 24, so that is automatically filled when I hit "Export"?

You've gone above and beyond already, so I understand if the above is too much of an ask...

 

[mumps]

Remove the previous macro form the worksheet code module and place the macro below in a standard module. Insert a button on your sheet and assign the macro to it.

Sub CompleteLog()
    Application.ScreenUpdating = False
    Cells(Rows.Count, "B").End(xlUp).Offset(1, 0) = Date
    If Range("E7").Value - Range("E8").Value > 0 Then
        Cells(Rows.Count, "C").End(xlUp).Offset(1, 0) = "LONG"
    Else
        Cells(Rows.Count, "C").End(xlUp).Offset(1, 0) = "SHORT"
    End If
    Cells(Rows.Count, "F").End(xlUp).Offset(1, 0).Resize(1, 6).Value = Array(Range("E12").Value, Range("E7").Value, Range("E8").Value, Range("E14").Value, Range("E9").Value, Range("E11").Value)
    Application.ScreenUpdating = True
End Sub