Pasting a selection 5 columns to the left

Hmerman

Board Regular
Joined
Oct 2, 2016
Messages
102
Hello All,

I am using Find & Select to Find a specific text string (e.g. "Home") in a column with 16000 rows.
Once the selection is found I Select all and copy the selection using Ctrl+C.
*I do this without using vba.

What I want to do is to paste that selection as it is, within the same rows to a column 5 rows to the left. For instance pasting row 100:120,200:202 ect in Column F to row 100:120,200:202 ect in Column A.
Using vba.

My code does not yield the desired result.
Code:
Selection.Offset(0, -2).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=True, Transpose:=False
Can someone please assist me in this endeavour?

Friendly Regards
Herman
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Code:
Selection.Offset(0, [B][COLOR=#FF0000]-2[/COLOR][/B]).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=True, Transpose:=False

Minus two?
 
Upvote 0
Hello,

Thank you, I changed the offset to -5.

Code:
Selection.Offset(0, [B]-5[/B]).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=True, Transpose:=False

It still does not work.

I tried a variation of the code:
Code:
Selection.Offset(0, -5).Select

Selection.Paste

This also did not work.
 
Last edited:
Upvote 0
Try this:
Code:
    Selection.Copy
    If Selection.Column > 5 Then Selection.Offset(0, -5).PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
 
Upvote 0
Try this:
Code:
    Selection.Copy
    If Selection.Column > 5 Then Selection.Offset(0, -5).PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
Why not just this single line of code in place of the three lines of code that you proposed...

If Selection.Column > 5 Then Selection.Offset(, -5).Value = Selection.Value
 
Last edited:
Upvote 0
Hi,

I tried it and it did not work. Thank you.

All the codes I have tried so far, selects the rows 5 columns to the left (which I want the code to do)...

But critically it does not paste the selection: I constantly get the error '1004' whereby <i>"That command cannot be used on multiple selections"</i>.

How do I tell excel to paste the multiple selections?
 
Upvote 0
Hi,

I tried it and it did not work. Thank you.

All the codes I have tried so far, selects the rows 5 columns to the left (which I want the code to do)...

But critically it does not paste the selection: I constantly get the error '1004' whereby <i>"That command cannot be used on multiple selections"</i>.

How do I tell excel to paste the multiple selections?
Ah, now I see where you said "Once the selection is found I Select all..." meaning you have non-contiguous ranges in your selection. Give this a try...
Code:
Sub CopyLeftFiveColumns()
  Dim Ar As Range
  For Each Ar In Selection.Areas
    If Ar.Column >= [B][COLOR="#FF0000"]2[/COLOR][/B] Then Ar.Offset(, -[B][COLOR="#FF0000"]2[/COLOR][/B].Value = Ar.Value
  Next
End Sub

Edit Note: I change the 5 originally posted, and which you originally asked for, to 2 as your last post indicated you actually wanted.
 
Last edited:
Upvote 0
Why not just this single line of code in place of the three lines of code that you proposed...

If Selection.Column > 5 Then Selection.Offset(, -5).Value = Selection.Value


I tried:
Code:
If Selection.Column > 5 Then Selection.Offset(, -5).Value = Selection.Value

It did the trick.

One more question: Will the code work if I paste from outside a table into a table?

Thanks for all the contributions and know how.

Friendly Regards
Herman
 
Last edited:
Upvote 0
I tried:
Code:
If Selection.Column > 2 Then Selection.Offset(, -2).Value = Selection.Value

It did the trick.

One more question: Will the code work if I paste from outside a table into a table?

Thanks for all the contributions and know how.
No, it did not work correctly because is was not designed to handle non-contiguous ranges (look at the second and beyond sub-ranges... you will see they are not correct). See Message #7 for code that will work.
 
Upvote 0

Forum statistics

Threads
1,217,452
Messages
6,136,724
Members
450,025
Latest member
Beginner52

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top