copy, paste and shift one down

  • Hey guys,


    I posted a thread before but I figured out some of the stuff on my own and I am left with one more thing which I need to ask you guys.


    I have two fields on my spreadsheet that update pretty much every second. I want to copy their values from one sheet into another and do this continously, but shift one row down every time it pastes data, so the old data stays on sheet2 whereas sheet1 keeps on updating.


    To be more specific...


    On the spreadsheet I have uploaded, I would like to copy cell G9 and H9 from Sheet1 to B2 and C2 of the sheet called "Chart". Once this happens I want the macro to copy G9 and H9 again and paste it into B3 and C3, and keep on shiffting down as it gets data from sheet1.


    Also, it would be nice to have the macro do the copy pasting every 5sec.


    Thank you very much for your help.

  • Re: copy, paste and shift one down


    sevan17,


    Try this routine


  • Re: copy, paste and shift one down


    Thank you very much.


    I am unfortunatley not on the computer with the data feeds, but I will try out your code tomorrow.


    One question though, is there any way we can put a 5sec delay inbetween each copy paste?


    It would be really cool to actually have a drop down menu with different types of time delays to choose from. But I am afraid to ask for that :)


    Thank you again. I will let you know tomorrow if it worked.

  • Re: copy, paste and shift one down


    sevan17,



    Well the code as written works only once. However, it can be made to loop. If you want it to loop and you want to vary the amount of time between cycles, then this is my suggestion.




    • On the chart sheet I'd create a named range with a name like cycleTime
    • Then in a cell comment

      • Indicate that the cell value needs to be a whole number integer { i.e. 3,4,5 . . }
      • Indicate that the value controls how often the report will retrieve the live data
      • Indicate that the value is indicative of the number of desired seconds
      • Indicate that the minimum value can be no lower than 2 and that values entered that are non numeric and or less than two will terminate the data retrieval


    • Then by some method be it a button click or workbook open event call the below routine.





    This code has not been tested and the rescheduling should be on the money but hey we're all human.


    You may also want to consider making a button to forcibly stop the routines instead of just changing the value of the 'cycleTime' range. To do this you need to store the next execution time of the routine so that it can be cancled.

  • Re: copy, paste and shift one down


    I appreciate your help but I am a little bit lost, I am a newbiw so you got to be more specific with the way you explain things.


    Also, how woudl I just put a simple loop on the first code you sent me.


    Thanks

  • Re: copy, paste and shift one down


    You should be able to use the last bit of code from iwrk4dedpr's last post with his first post.



    Change the "00:00:" to the specific interval you want. This would be a static amount not one where you could pick a time interval.

  • Re: copy, paste and shift one down


    I tried your code and changed the time to 00:00:02


    I got a syntax error on this line:


    Application.Ontime Now() + timeValue("00:00:02" ) "TransferIndexRatios"

  • Re: copy, paste and shift one down


    sevan17,


    Quote

    Also, how woudl I just put a simple loop on the first code you sent me.


    You don't want to loop. It'll needlessly eat up cpu time.


    Use the code that I posted with the OnTime event. However, I did have a typo in the code. I missed a comma ( , ). So change the following line of code from


    Application.Ontime Now() + timeValue("00:00:" & Range("cycleTime")) "TransferIndexRatios


    to


    Application.Ontime Now() + timeValue("00:00:" & Range("cycleTime")), "TransferIndexRatios




    If you remain with this line of code then you will be able to set multiple refresh intervals.

  • Re: copy, paste and shift one down


    Just using the code won't work, right? I am not to clear on the first 3 steps that you explained before, can you be more specific on those so I know what to do? Thanks

  • Re: copy, paste and shift one down


    As the cells on Sheet1 change, the macro just overwrites the values in sheet "Chart" that it pasted before. It will just wait the given time delay and just copy the fields from Sheet 1 and paste it twice into the Chart sheet.


    I want it to copy paste it every x second and then do it again but pasting it into the next row.

  • Re: copy, paste and shift one down


    sevan17,


    Ok in order to more fully explain how to use the code that I've supplied I need one more piece of information from you.



    How is the data on "Sheet1" updated?

  • Re: copy, paste and shift one down


    Well, the fields that I want to copy paste are G9 and H9. They are calculated by the data given in the above fields. The raw data is feeded by a vendor (Bloomberg).


    Also, read my previous thread, I changed it, I think the way the macro is set up right now is not correct...

  • Re: copy, paste and shift one down


    Quote from sevan17

    Well, the fields that I want to copy paste are G9 and H9. They are calculated by the data given in the above fields. The raw data is feeded by a vendor (Bloomberg).


    Also, read my previous thread, I changed it, I think the way the macro is set up right now is not correct...



    This I realize.


    What I need to know is. How does the data supplied by Bloomberg get into the workbook.


    You can have a DDE ( Dynamic Data Exchange ) ( which I'm not familiar with ), the data can be typed in by hand ( your original post excludes this ), or the data can be retrieved via a web query.


    I need to know the method by which the Bloomberg data is input into this workbook.

  • Re: copy, paste and shift one down


    I have two Bloomberg add-ins installed. Their data is feeded into a server which is located in an office in Hawaii and we get the data from the server there. I don't more than that..I am sorry.


    I think the macro is still not correct. This is what it should look like:


    1. Copy G9 and H9 from Sheet1 and paste it into B2 and C2 of sheet "Chart"
    1. Copy G9 and H9 from Sheet1 and paste it into B3 and C3 of sheet "Chart"
    1. Copy G9 and H9 from Sheet1 and paste it into B4 and C4 of sheet "Chart"
    ................
    ................
    ...........
    .....
    Just keep on shifting down one row every time it pastes one row.

  • Re: copy, paste and shift one down


    sevan17,


    Hey I need to break off for a bit. However, in a couple of hours I'll be available again, and actually if you'd like to talk ( I'm better verbal than written any day ) I can pm you with where I can be reached if you're ok with a long distance call.


    I'll be back in about around 5 pm my time ( MST ) sorry...!

  • Re: copy, paste and shift one down


    I would love to do that but I am not going to be at work anymore. I don't have access to all the datafeeds and stuff at home to work on this. The markets in Japan will open in about 1 1/2 hours and I thought I could maybe get it running by then.


    I am satisfied with the macro the way it is, except that it won't shift down every time it copy pastes.

  • Re: copy, paste and shift one down


    Ok as a quickie. During the course of the posts I've seen some errors so. Replace the original routine with this one.



  • Re: copy, paste and shift one down


    I am fien with this:



    but again this will do the copy paste once and after that it will just overwrite the values instead of shifting down every time he does the copy and pasting.


    Thanks a lot for the help.

  • Re: copy, paste and shift one down


    sevan17,


    Man am I a block head.



    Use this:

  • Re: copy, paste and shift one down


    Oh man it works, thank you so much. I will now look into the more advanced version of yoru macro with the cycle changes.


    How/Where exactly do I set the cycle?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!