Unable to insert (rows) from Clipboard into needed rows below a variable selected row

I'm only showing the code to see how I'm getting the data (rows) into the clipboard.

We use this Macro because it easily copys multiple non-adjacent selected rows from one worksheet to the clipboard. We can then left click, insert, then select down but I do it so often it would be helpful to have a button or keystroke Macro after just selecting the row where I need the data (rows) inserted.

Sub CopytoClipboardandDelete2()


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


    Sheets.Add After:=Sheets(Sheets.count)
    ActiveSheet.Paste
    Selection.Cut
    ActiveWindow.SelectedSheets.Visible = False


End Sub

Re: Unable to insert (rows) from Clipboard into needed rows below a variable selected

Sure! Sorry. :hammerhe:

Re: Unable to insert (rows) from Clipboard into needed rows below a variable selected

yeah we use flat files; tab and comma text files. why? they open great in excel

Re: Unable to insert (rows) from Clipboard into needed rows below a variable selected

do you mean a workbook with the macro in it? The macro is shown really just to show how the data is getting to the clipboard, that's it. I'm hoping for a basic macro to take any data from the clipboard and insert AT and down from any row that is selected.

Re: Unable to insert (rows) from Clipboard into needed rows below a variable selected

Quote from p45cal;774414

It's a macro with one line:

Selection.Insert Shift:=xlDown

I wish it was this easy. That's the first thing I tried. It doesn't matter whether I cut, copy or capture row with the Macro above to the clipboard; running this Macro only inserts one blank line and clears the clipboard. This will even insert as many blank rows as rows that I highlight, but still won't insert the 4 rows in the clipboard since as soon as the Macro is run the clipboard is cleared.

Re: Unable to insert (rows) from Clipboard into needed rows below a variable selected

That makes sense because commenting out that last line just leaves the copied rows, at least together, on sheet2 showing copied. This is the benefit of this Macro, it gets around the inability to 'cut' multiple non-adjacent rows at once. The trouble is, either way, as soon as I run the the second Macro (Selection.Insert Shift:=xlDown) the copy/cut ends and the clipboard clears.

The module code is in a .bas file in my Personal.xlsb template that holds all my Macros. It's only run when selected. When opening any tab txt file I just use the text import wizard.

This is just mind-boggling that there's not a simple Macro similar to (Selection.Insert Shift:=xlDown) that can preserve the clipboard and INSERT the contents anywhere below a highlighted row shifting down without the extra click on the popup to select the down direction. :roll:

Re: Unable to insert (rows) from Clipboard into needed rows below a variable selected

I'm not sure I understand. For sure I'm not sure where to put the stop code. I get an error wherever I put it.

The reason I know it runs and when is because I'm doing this isolated, not calling it within another Macro right now.

So for e.g. I highlight rows 5,6,8,20 and 22 holding the Ctrl button of course.

Then I run the Sub CopytoClipboardandDelete2 and the rows delete and go to the clipboard. Now I can paste anywhere or insert right clicking, insert and clicking down.

My hope is to just highlight the row run this new Macro from a button and the rows on the clipboard automatically insert and shift all rows below that, down.

Here's a screenshot of how I have my Macros setup.

[ATTACH=CONFIG]69779[/ATTACH]

Re: Unable to insert (rows) from Clipboard into needed rows below a variable selected

Thank you so much for your patience! omg I had a feeling it was something stupid. As soon as you made a point about using shortcuts I tested what happens during different copy/cut/paste scenarios and sure enough; the moment I click on the main either 'all macros' button in the toolbar or quick access the clipboard is cleared!!! Not until trying different template formats and keeping an eye on the variations of the macro names because I have had issues with that.

Plus I learned the difference between non-adjacent and non-contiguous rows thanks to you!

Anyway thanks again p45cal! You must have been thinking, what the hell is wrong with this guy!? :smile: