Share via

Excel no longer pastes filtered (visible) cells correctly

Anonymous
2024-02-04T17:07:49+00:00

A really nice feature of Excel used to be the ability to copy and paste only cells that were visible, after a column had been filtered. But recently, Excel's behaviour has changed and it will not copy and paste filtered cells correctly.

In the attached example, I have filtered column1 so it only shows item9. When I copy column1, I want to paste the visible cells into column2. But instead of pasting 10 instances of item9 into column2, it had only pasted 4 instances.

How can I revert back to the previous behaviour of Excel in which it would copy and paste visible cells?

Microsoft 365 and Office | Excel | For home | MacOS

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Rory Archibald 18,885 Reputation points Volunteer Moderator
    2024-02-06T16:42:27+00:00

    You could use something like this:

    Sub CopyVisibleRanges()

    Dim CopyRange As Range

    On Error Resume Next

    Set CopyRange = Application.InputBox(Prompt:="Select cells to copy", Title:="Copy from", Type:=8)

    If Not CopyRange Is Nothing Then 

      Set CopyRange = CopyRange.SpecialCells(xlCellTypeVisible) 

  Dim pasteRange As Range 

  Set pasteRange = Application.InputBox(Prompt:="Select first cell for pasting", Title:="Paste To", Type:=8) 

  On Error GoTo whoops 

  If Not pasteRange Is Nothing Then 

     Application.ScreenUpdating = False 

     Dim colOffset As Long 

     colOffset = pasteRange.Column - CopyRange.Column 

     Dim area As Range 

     For Each area In CopyRange.Areas 

        With area 

           .offset(, colOffset).Value = .Value 

        End With 

     Next area 

  End If

    End If

    clean_up:

    Application.ScreenUpdating = True

    Exit Sub

    whoops:

    MsgBox "Error: " & err.Number & vbLf & err.Description

    Resume clean_up

    End Sub

    Note this has to do the copying too.

    1 person found this answer helpful.
    0 comments

23 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2024-02-05T08:00:40+00:00

    When I copy item9 from rows 6,8,9,11,16,21,25,26,27,31 in column1, I need item9 to be pasted into rows 6,8,9,11,16,21,25,26,27,31 in column2.

    This is how Excel used to work up until very recently, but something has changed. A quick Google suggests other people have been frustrated by this change in behaviour.

    How can I go back to the previous behaviour?

    7 people found this answer helpful.
    0 comments
  2. Anonymous
    2024-02-06T09:14:48+00:00

    If you read through the various threads scattered around the internet, they have often panned out the same way this thread has. Some people adamant that Excel has never worked with visible cells only, others confident of a change in behaviour.

    Regardless of whether or not this has previously been possible, I'm still left with the same possible. I never ever want to paste into hidden cells, I only ever want to work with visible cells. There must be a way to do this?

    6 people found this answer helpful.
    0 comments
  3. Anonymous
    2024-02-05T12:16:56+00:00

    Other people who have asked same question on internet have also been given that response.

    The behaviour of Excel was absolutely as I described - when filtered it would only work with cells that were visible. It would not paste into cells that were hidden. I work with it for hours every day and the behaviour has changed very recently. How can I return to previous behaviour?

    For example in this thread a Volunteer Moderator has replied with 'I don't think that it ever did the above', which isn't correct:

    https://answers.microsoft.com/en-us/msoffice/forum/all/excel-2016-pasting-onto-filtered-sheet-pastes-into/f177b5a6-2ce5-425d-9c8c-0697381fe965

    6 people found this answer helpful.
    0 comments
  4. Anonymous
    2024-02-06T08:23:25+00:00

    4 people found this answer helpful.
    0 comments