VBA with multiple selection.copy not registering

  • I have a macro that takes the current selection, copies it, paste's it in a certain cell, copies it again (unsure if that is necessary) paste's again forming a list then its supposed to copy a range that i paste outside the program (non Microsoft program). The issue im running into is that in excel it shows the final range selected and copied (Green boarder with white "ants" circling around) however when i switch programs to paste the data, the original cell i copied is whats pasted.



  • If you switch ScreenUpdating off then you mist restore it.


    It's not exactly clear what you are doing, but try this


  • thank you RoyUK. It is now retaining the right information on the clipboard a third of the time during my tests. If i revise line 12 of your code "copy" to "select" then manually "Ctrl+C" then switch programs and paste it works flawlessly the entire time

  • RoyUK correct, if it didnt glitch then the "select" wouldn't be needed. however to diagnose where it's having its issue i replaced "copy" with "select" then after the macro has run i "Ctrl+C" then paste elsewhere and it works just fine. Which leads me to understand there is an issue with the last "copy" command. The weird part is that its not consistent, some instances the final copy command works, and some not.

  • The data all comes from various places in the same workbook. The cell's being copied are formula's. Could that have something to do with it? If I add to the end of the macro to paste the copied range into a blank area of the same worksheet it works no problem.

  • You need to use the sheet's name if your data is on multiple sheets. Also, do you need to paste formulas or values.

    Code
    1. Sheets("this sheet name").Range("AP2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    2. :=False, Transpose:=False

    Can you attach an example workbook and let me know on which sheet each range is.