I have information in Excel on several rows. I want to copy and paste selected cells and keep each cell value on that row that it was copied from. But each time I copy the values they get clustered together on rows next to each other.

I have included an image to illustrate what I want and how it actually turns out. I select the cells in column B with the value "Banana" (B2 and B4). When I paste I want them to look like in column D (D2 and D4). But what actually happens is the result in column F (F2 and F3).

I use Excel 2016.

enter image description here

1 Answer 1


That behavior is by design; when you select noncontiguous cells and copy them, Excel doesn't remember their original location.


  • Copy the values one at a time to the target cells.
  • Copy the entire column first, then filter it to exclude all cells with "Banana" and delete the contents from every other row.
  • Use formulae to check whether "Banana" is in the same cell on the other sheet, and only return a value when it is. This formula should work: =IF(B2="Banana",B2,"")
  • Use a macro to look at all cells in column B, and duplicate the value to column D if it says "Banana" Sub BananaCopy() Const csSrc As String = "B" Const csTgt As String = "D" Const csFruit As String = "Banana"

    Dim rngFruits As Range, rngCell As Range
    Set rngFruits = Range(csSrc & 1, csSrc & Rows.Count)
    For Each rngCell In rngFruits
        If rngCell.Value = csFruit Then
            Range(csTgt & rngCell.Row).Value = csFruit
        End If
    Next rngCell

    End Sub

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .