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
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Ruddles

Well-known Member
Joined
Aug 24, 2010
Messages
5,786
Code:
Selection.Offset(0, [B][COLOR=#FF0000]-2[/COLOR][/B]).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=True, Transpose:=False

Minus two?
 

Hmerman

Board Regular
Joined
Oct 2, 2016
Messages
102
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:

Tetra201

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,631
Try this:
Code:
    Selection.Copy
    If Selection.Column > 5 Then Selection.Offset(0, -5).PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,690
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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:

Hmerman

Board Regular
Joined
Oct 2, 2016
Messages
102
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?
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,690
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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:

Hmerman

Board Regular
Joined
Oct 2, 2016
Messages
102
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:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,690
Office Version
  1. 2010
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,806
Messages
5,598,188
Members
414,218
Latest member
speedbit

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
Top