none
VBA modifying a code to place a cell text in another sheet (also) RRS feed

  • Question

  • Hello all

    We have this sample file:
    http://www.mediafire.com/file/6aoar08i1degrk2

    There are 3 visible sheets:

    1) Account
    2) Portfolio
    3) PasteSymbolSheet

    The process to download open positions (once the workbook is connected) is to subscribe to Account updates by clicking the "Request Account Updates" button from the "Account" sheet.



    Once subscribed, all open positions (and new ones) are/will be downloaded directly to the "Portfolio" sheet.




    What we want is simply to place also the symbol (only) in the "PasteSymbolSheet" every time there is an update (a new position. In other words every time the Portfolio sheet adds a new row), without changing the active sheet. 




    Can you please help?

    • Edited by NicoPer Sunday, December 11, 2016 8:58 PM
    Sunday, December 11, 2016 8:54 PM

Answers

  • Hi NicoPer,

    use the code mentioned below.

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim FinalRow, FinalRow1 As Long
    Dim ws, ws2 As Worksheet
    Set ws = ActiveWorkbook.Sheets("Sheet1")
    Set ws2 = Worksheets("Sheet2") 'Change this to the name of the new worksheet you want the data pasted to
    FinalRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
    FinalRow1 = ws2.Range("A" & ws2.Rows.Count).End(xlUp).Row
    FinalRow1 = FinalRow1 + 1
        ws.Range("A" & FinalRow).Copy
            ws2.Range("A" & FinalRow1).End(xlUp).PasteSpecial xlPasteValuesAndNumberFormats
    
    
    End Sub

    you need to change the sheet name in above mentioned example.

    paste this code in "Portfolio" sheet on "Worksheet_Change" event.

    when data get added in the portfolio sheet then it will run the above mentioned code and add the symbol to the "pastesymbolsheet" automatically.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, December 12, 2016 2:03 AM
    Moderator

All replies

  • Hi NicoPer,

    use the code mentioned below.

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim FinalRow, FinalRow1 As Long
    Dim ws, ws2 As Worksheet
    Set ws = ActiveWorkbook.Sheets("Sheet1")
    Set ws2 = Worksheets("Sheet2") 'Change this to the name of the new worksheet you want the data pasted to
    FinalRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
    FinalRow1 = ws2.Range("A" & ws2.Rows.Count).End(xlUp).Row
    FinalRow1 = FinalRow1 + 1
        ws.Range("A" & FinalRow).Copy
            ws2.Range("A" & FinalRow1).End(xlUp).PasteSpecial xlPasteValuesAndNumberFormats
    
    
    End Sub

    you need to change the sheet name in above mentioned example.

    paste this code in "Portfolio" sheet on "Worksheet_Change" event.

    when data get added in the portfolio sheet then it will run the above mentioned code and add the symbol to the "pastesymbolsheet" automatically.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, December 12, 2016 2:03 AM
    Moderator
  • Hi Deepak and thank you very much for steeping in.

    I´ve tried to use the worksheet change in the Portfolio sheet before and that didn´t work.

    It seems that when it downloads a position, it fills one cell at a time.
    And that´s triggering the macro every single time unnecessarily.


    Right now it´s downloading and placing all rows/columns in the Portfolio sheet.

    But isn´t it possible that it *also* place the symbol in another sheet that way?
    Without forcing a worksheet change I mean.

    Monday, December 12, 2016 2:59 AM
  • Hi NicoPer,

    you had mentioned that,"I´ve tried to use the worksheet change in the Portfolio sheet before and that didn´t work."

    did value get copied or not?

    the other thing you had mentioned that,"But isn´t it possible that it *also* place the symbol in another sheet that way? Without forcing a worksheet change I mean. "

    you need to modify your code.

    open the Sheet11(portfolio) code.

    go to the "ExerciseOptions_Click()"

    the value for the symbol assigning from the line mentioned below.

     .symbol = UCase(Cells(id, Columns(COLUMN_PORTF_SYMBOL).column).value)

    and then go to "UpdatePortfolio()"

    in the line mentioned below they assign the value of symbol to the sheet.

     Cells(portRowId, Columns(COLUMN_PORTF_SYMBOL).column).value = .symbol

    so please refer the below image

    so as per my suggestion try to add the code there to assign the value of symbol to other sheet.

    I am not able to run the code so I don't know how values are coming.

    if the code is not written by you then you can contact the developer of the code to modify the code.

    if you are not able to contact the developer and want to perform this task then my suggestion in the last post can work for you without make any changes in the current code.

    I don't have the object libraries that your code use so I am not able to run the code and test the sheet.

    so try to implement the suggestion and let me know about that.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Tuesday, December 13, 2016 2:33 AM
    Moderator
  • Hi Deepak

    I´m very sorry for the confusion. I´ve should told about the worksheet change and why it was discarded.
    The current excel file is doing too many things at once and I found that when I use it, Excel completely freezes.

    I understand that we´re blind as we cannot see how the values are coming.
    I´m asking here because the IB API is kind of for advanced users and they have almost no support.
    Still, since the code is in the file, I thought we could use it to place the data elsewhere.

    ---


    I didn´t fully get your suggestion. Can you please explain a bit further?
    Which code should I place where? I´m sorry, I´m practically a zero at VBA.
    Tuesday, December 13, 2016 10:06 PM
  • Hi NicoPer,

    As I already informed you earlier. you just need to open the code reside in Portfolio.

    Then find "ExerciseOptions_Click()"

    in that you will find below line.

     .symbol = UCase(Cells(id, Columns(COLUMN_PORTF_SYMBOL).column).value)

    this is the line from where value is coming for the symbol.

    so try to debug the code and find the value of .symbol.

    or you can try to print the value on immediate window with the below line.

    Debug.Print (UCase(Cells(id, Columns(COLUMN_PORTF_SYMBOL).column).value))

    so it will print the value on immediate window.

    in the below mentioned line the value of symbol assign to the column.

    Cells(portRowId, Columns(COLUMN_PORTF_SYMBOL).column).value = .symbol

    so you have to add the code just after this line.

    first I need to confirm with you that how data are coming. it is coming for the whole range or one data at one time.

    so try to use above mentioned debug.print line and let me know about the result. after that we can try to assign the value to other sheet.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, December 14, 2016 5:22 AM
    Moderator
  • Hi Deepak

    So I run some tests with no luck. But then I found a simpler way to do what I want without messing with the code.

    Thank you very much anyways.

    This thread is no longer needed. Mods could close/delete it wanted.

    Thursday, December 15, 2016 2:54 AM
  • Hi NicoPer,

    it's good to hear from you that you got the solution for your issue by yourself.

    you had mentioned that you want to close this thread.

    so please try to mark your last post as an answer which confirms that your problem is solved.

    if you don't mark your post as an answer then this thread will be open forever.

    so help us to close this thread.

    if possible then you can share the solution with our forum.

    so it will help others in future who will have same issue like yours.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, December 15, 2016 5:25 AM
    Moderator