vba macro: I'm losing 'end of row' lin cut/pasting multiple (non-adjacent) rows

  • Re: vba macro: I'm losing 'end of row' lin cut/pasting multiple (non-adjacent) rows


    Not sure why I can't get InsertClipboardUnderSelectedRow macro above to work once I have row data on my clipboard using your SelectionToClipboard


    I got this to work, just can't INSERT, only paste or over-write data.


    This does allow me to call FormatClipboardPaste and get that done at the same time, so I don't forget. (but still having that issue I mentioned, scenarios where I paste only column A, see below)




    Even when using FormatClipboardPaste, there are still several scenarios where your SelectionToClipboard still pastes ONLY data in column A for all the rows.


    i.e. SelectionToClipboard from source file to new workbook, run FormatClipboardPaste, then highlight any number of rows and paste back to original source worksheet and only the data from column A pastes. Data from column B through Q or whatever is gone. :(


    Anyway, if you have a little more time some point. I'd love to get a couple last things worked out.


    Thanks again!
    Mark

  • Re: vba macro: I'm losing 'end of row' lin cut/pasting multiple (non-adjacent) rows


    I had another problem, but I'm either blacklisted or everyone left the board! :duck:


    This is the other issue and it applies to both SelectionToNewWB and SelectionToClipboard.


    It errors on this line: Application.Goto .Cells(1, 1) but ONLY, and always, on one condition and the data is lost so I have to start over back to the last save. The problem always stems from including row 1 in the selection, but ONLY when all contiguous are also selected! Then error and proof! The data is gone. i.e. say there's data in row 1-5 and rows 10-15 though these don't impact anything, just to know that there's a gap of no data from row 6-9 and if row 1 is instead empty there's no issue. As soon as all rows 1-5 are selected with no data in row 6 it errors. I tried a few different combinations, but no luck.


    The only other stresser is the moving data back and forth a few times, I mentioned, even running FormatClipboardPaste and inevitably a paste is going to result in only the row data in column A pasting.


    I hope someone can help. I've put these to use in a few different macros.


    Thanks so much,
    Mark

  • Re: vba macro: I'm losing 'end of row' lin cut/pasting multiple (non-adjacent) rows


    KjBox provided the fix for the error when selecting row 1 along with all contiguous rows below until open row. i.e. anytime you select ALL rows left when data is in row 1.


    Change this line (in both macros) Application.Goto .Cells(1, 1) to Application.Goto AWB.ActiveSheet.[a1]


    Yippee!

  • Re: vba macro: I'm losing 'end of row' lin cut/pasting multiple (non-adjacent) rows


    Pleased we got there in the end!

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.