Pasting a selection 5 columns to the left

[Hmerman]

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.

Selection.Offset(0, -2).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=True, Transpose:=False

Can someone please assist me in this endeavour?

Friendly Regards
Herman

 

[Hmerman]

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)...

You are correct.

I tried:

Sub CopyLeftFiveColumns()
  Dim Ar As Range
  For Each Ar In Selection.Areas
    If Ar.Column >= 5 Then Ar.Offset(, -5).Value = Ar.Value
  Next
End Sub

Your code cycles through all the non-contiguous selections and pastes it precisely where I want it.

Thanks a million.

Have a gr8 day in Usa.

Friendly Regards
Herman

 

[Rick Rothstein]

Just to show why previous attempts did not work.

Of course none of our proposals worked because we all missed that the OP was describing the selection of non-contiguous ranges in his original message. I am pretty sure what I posted in Message #7 will do what the OP actually asked for.

 

[Tetra201]

... we all missed that the OP was describing the selection of non-contiguous ranges in his original message. I am pretty sure what I posted in Message #7 will do what the OP actually asked for.

Agree.