Trying to automate the copy/"cut" process of multiple selections using a VB macro

Hello there!

I'm trying to automate a process in Excel 07 using VB.

The problem stems from a copy/paste bug, it appears. Copy and pasting multiple selections using the Copy/Paste command buttons and keyboard shortcuts, using the system clipboard is fine. I always have to go back and delete the selection in the previous workbook/sheet, which is why I'm trying to write a VB, but at least it works.

The copy/paste, even cut/paste, using the Microsoft Clipboard, that holds multiple entries, is the problem. First, cutting leaves the contents as is in Excel, but the real issue is this. When you highlight and copy multiple selections, say rows 1 through 4 and 7 through 10, what actually goes to the clipboard in the pane is all rows 1 through 10! I was hoping this might be a workaround to save a few keystrokes and then make a VB script possibly doable. And no I'm not using the Paste All option in case you're wondering.

Of course the trouble in copying and pasting multiple selections and deleting them BEFORE pasting them to another book sheet or place in the same sheet, in essence a "cut", which is an inherent behind the scene issue with Excel that I somewhat understand.

Doing some research, it appears a VB script will do what I need.

**** All I really need to be able to do is manually select (highlight) multiple rows (say 5 through 8, 12 through 15 and 27 through 34), different every time, and copy them to another book or sheet and at the same time delete the original selected rows, so I don't have to go back and delete them.

Something as simple as below might work, but I'm horrible at setting dynamic or variable code that runs the script only on the data that I've manually selected. I run into this problem with every script I've ever used or written!

Please Login or Register  to view this content.

This seems pretty close as well: http://www.ozgrid.com/forum/showthread.php?t=160498

Of course, not wanting to use an input box defeating the purpose.

Please Login or Register  to view this content.

If anyone can provide some insight that would be wonderful.

Thank you so much,
Mark

Ultimately, I'm hoping that the data can go to the clipboard so I can control where it goes vs just going straight to, say a blank workbook, which is why I thought I needed Microsoft Office's clipboard to work!

Thanks for a start Crooza!

A few things. Keep in mind I do have to create a sheet 2 first, but the real trouble is that it doesn't delete the original selections.

As far as understanding what I'm looking for, the only thing you might be missing, and I don't know if it's possible, is I would like to move the selected data to a clipboard, either the system (which I don't think will work) or to the built in Office clipboard. The reason for this is to avoid a 2nd step moving the results again, similar to going back and deleting the selection manually. Each time the data needs to be moved to another specific workbook, sometimes a new workbook, sometimes another existing workbook.

Yes sir, I just tried it again. fwiw I'm using Excel 07

Right away on line: Dim obj As New MSForms.dataobject

I get a complile error: User-definded type not defined
"You tried to declare a variable or argument with an undefined data type or you specified an unknown class or object name."

Here's what I'm doing for clarification. I have an open workbook, I select several non-contiguous rows and then run the macro, hoping all the selected rows goes to the Office clipboard, vs the system clipboard, then the original rows deleted and I can paste those rows into another workbook. Does that make sense?

The problem with this goes back to what I mentioned earlier. If the system clipboard is used, the moment the rows are deleted, before copying, the are deleted from the system clipboard too. And when the rows (say rows 1 through 4, row 7 through 8 and 13 through 18) are copied to the built in Office Clipboard during this process, what's actually copied to the clipboard is ALL contiguous rows from row 1 to row 18! Urgh!!!

I wonder if "shift and F8" can help, though I wasn't able to get it to work?

I'm SO sorry about this! I didn't find it but browsed for the file FM20.dll and viola! THANK YOU!

The only problem is on the paste back into another Excel worksheet. I should have mentioned that the data is tab delimited and when pasted into Word the data, say 10 rows, are all displayed one after the other, just the fields A1, A2, A3 etc. aren't tab delimited of course.

There's an additional problem when pasting back into Excel. It appears that the line breaks are not preserved either. Row 1 goes into any row on the new worksheet I paste (probably space delimited) but the 2nd row doesn't start until column v or so and the next row further right, and so on. If I select 10 rows is pastes into only 6 total and 15 rows pasted into only 8 rows on the new worksheet.

Also http://www.ozgrid.com/forum/showthread.php?t=198459