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

[wittonlin]

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!

Sub DynamicCutPaste()

    Range("2:2,4:4,6:6,8:8,11:11").Select
    Range("A11").Activate
    Selection.Copy
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp

 End Sub

This seems pretty close as well: Cut Multiple Selection, Paste Pieces Together and Delete Original Rows

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

Sub uittoets()
     
    Dim rowCount As Integer
    Dim i As Integer
    Dim nName As Name 'vir #REF delete
    Dim rng() As Range
    Dim AantalSeleksies As Integer
     
     'get amount of selections
    AantalSeleksies = Selection.Areas.Count
     
    Redim rng(1 To AantalSeleksies) As Range 'skep variables
     
    For i = 1 To AantalSeleksies
        Set rng(i) = Selection.Areas(i)
        ActiveWorkbook.Names.Add Name:="MyRange" & i, RefersTo:=Selection.Areas(i)
        rowCount = rowCount + Selection.Areas(i).Rows.Count
    Next i
     
     'Select selections
    Selection.Copy
    Range("a24").PasteSpecial (xlPasteAll)
     
    For i = 1 To AantalSeleksies
        Range("myrange" & i).Select
        Range("MyRange" & i).EntireRow.Delete
    Next i
     
     'Delete #REF names
    For Each nName In Names
        If InStr(1, nName.RefersTo, "#REF!") > 0 Then
            nName.Delete
        End If
    Next nName
     
End Sub

If anyone can provide some insight that would be wonderful.

Thank you so much,
Mark

 

[wittonlin]

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!

 

[wittonlin]

Here's a better explanation than the last post.

Ultimately, 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.

 

[wittonlin]

Any insight?

I've used this site more than any other, I thought for sure I'd get a response here.

 

[wittonlin]

If someone can help me just get started, I might be able to figure out the rest.

I can't find, an almost embarrasingly simple, code to run the macro only on a selected range of non-contiguous rows holding down Ctrl., that are different every time; without using a message box, based only on highlighting the rows, then running the macro.

I've not been able to get anything to work based on exhaustive searching! E.g. Selection.CurrentRegion.Select

If I just knew what nonexplicit variable can I use here? Rows("unknown until selected").Select and Range("unknown until selected").Activate ...that would be huge!

This seems SO simple, but I always find it based on some explicit variable or contingency (last populated, active cell, specific value, count, xlDown, xlRight, next empty, etc.), not simply based on the non-contiguous rows selected (hightlighted) that are different every time I run the macro.

Then I might be able to get something like this to work:

Sub Macro14()

    Rows("unknown until selected").Select
    Range("unknown until selected").Activate
    Selection.Copy
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp

End Sub

 

[wittonlin]

To add to the community for someone else in need of a similar solution. It's a totally different direction, but works perfectly!

Here's the solution from Crooza

Sub copytoclipboard()

Dim obj As New MSForms.dataobject
Dim x, str As String
Dim count As Integer

count = 0

For Each x In Selection

count = count + 1
    
If x <> "" Then
If count = 1 Then

    str = str & x
    Else
    str = str & Chr(9) & x

End If
End If

 If count = 16384 Then
        str = str & Chr(13)
    count = 0
    End If

Next

obj.settext str
obj.putinclipboard

Selection.Delete Shift:=xlUp

End Sub