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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.