Paste TO visible cells only in a filtered cells only

NSK-Mar 

Step 1: Select both the filtered source column and destination column

Step 2: Go to Home->Editing->Fill and select the direction of fill from source to destination column. The source and destination column doesn't need to be adjacent.

NSK-Mar I don't understand how it's possible that Google Sheets is far more sophisticated than Excel. Is there a solution to this yet?

NSK-Mar 

I'd like to add something to my answer above, about the “Sub CopyVisibleToVisible1” macro. If you want to use this frequently, you can assign the macro to a toolbar buttons. This is how:

• Copy the code:

Sub CopyVisibleToVisible1()
'use this for:
'Copy paste(value only):
'from filtered range to filtered range
'from filtered range to unfiltered range
'from unfiltered range to filtered range
'Not work on hidden column

    Dim rngA As Range
    Dim rngB As Range
    Dim r As Range
    Dim Title As String
    Dim ra As Long
    Dim rc As Long
    
    On Error GoTo skip:
    
    Title = "Copy Visible To Visible"
    Set rngA = Application.Selection
    Set rngA = Application.InputBox("Select Range to Copy then click OK:", Title, rngA.Address, Type:=8)
    
    Set rngB = Application.InputBox("Select Range to Paste (select the first cell only):", Title, Type:=8)
    Set rngB = rngB.Cells(1, 1)
    Application.ScreenUpdating = False

    ra = rngA.Rows.Count
    rc = rngA.Columns.Count
    If ra = 1 Then rngB.Resize(, rc).Value = rngA.Value: Exit Sub
    
    
    Set rngA = rngA.Cells(1, 1).Resize(ra, 1)
    
    For Each r In rngA.SpecialCells(xlCellTypeVisible)
      rngB.Resize(1, rc).Value = r.Resize(1, rc).Value
        Do
          Set rngB = rngB.Offset(1, 0)
        Loop Until rngB.EntireRow.Hidden = False
    Next
    
    Application.GoTo rngB
    Application.ScreenUpdating = True
    Application.CutCopyMode = False
    

Exit Sub
skip:
    If err.Number <> 424 Then
        MsgBox "Error found: " & err.Description
    End If
    
    Application.ScreenUpdating = True
    Application.CutCopyMode = False

End Sub

• Open VBA window (by pressing ALT+F11)
• Open PERSONAL.xlsb
• In module section, create a new module, say module1.
• Paste the code into module1.

Now assign the macro to a toolbar button. You can follow the instruction on this link:

https://www.excelcampus.com/vba/add-macro-buttons-excel-ribbon-toolbar/

The benefit of doing it this way:

1. The macro is available on any open workbook & easy to access.
2. You don’t need to put the macro on all your workbooks, just on PERSONAL.xlsb.

NSK-Mar 

We cant do it has values, but using simple formula we can achieve it as below:- 

Step 1:- Please copy values you wanted and paste them in separate excel and name each row with numbers in sequence.

Step 2:- Filter the cells to which the values needed to pasted , in another column i.e, not in the cells you want to paste update it with numbers in sequence .

Step 3: Use Vlook up with look up value from step2 sequence updated and look up array from Step 1 sequence , and use column as required . you will get the values.

Break the link of the file and go on.

Hope this is useful.

Thanks

Kanthrishi

NSK-Mar 

With this macro you can easily copy-paste values from & to filtered range. 

Sub CopyVisibleToVisible1()
'use this for:
'Copy paste(value only):
'from filtered range to filtered range
'from filtered range to unfiltered range
'from unfiltered range to filtered range
'Not work on hidden column

    Dim rngA As Range
    Dim rngB As Range
    Dim r As Range
    Dim Title As String
    Dim ra As Long
    Dim rc As Long
    
    On Error GoTo skip:
    
    Title = "Copy Visible To Visible"
    Set rngA = Application.Selection
    Set rngA = Application.InputBox("Select Range to Copy then click OK:", Title, rngA.Address, Type:=8)
    
    Set rngB = Application.InputBox("Select Range to Paste (select the first cell only):", Title, Type:=8)
    Set rngB = rngB.Cells(1, 1)
    Application.ScreenUpdating = False

    ra = rngA.Rows.Count
    rc = rngA.Columns.Count
    If ra = 1 Then rngB.Resize(, rc).Value = rngA.Value: Exit Sub
    
    
    Set rngA = rngA.Cells(1, 1).Resize(ra, 1)
    
    For Each r In rngA.SpecialCells(xlCellTypeVisible)
      rngB.Resize(1, rc).Value = r.Resize(1, rc).Value
        Do
          Set rngB = rngB.Offset(1, 0)
        Loop Until rngB.EntireRow.Hidden = False
    Next
    
    Application.GoTo rngB
    Application.ScreenUpdating = True
    Application.CutCopyMode = False
    

Exit Sub
skip:
    If err.Number <> 424 Then
        MsgBox "Error found: " & err.Description
    End If
    
    Application.ScreenUpdating = True
    Application.CutCopyMode = False

End Sub

NSK-Mar 
Here's a video can solve this problem
https://youtu.be/F0pUKDQZ9RI

NSK-Mar Another option would be to replicate your source column, then clear out all the values not shown in your filtered list ('reverse' the filter, then clear the values).  Then remove the filter from the table altogether, copy the entire column, finally do Paste Special and select Skip Blanks.  

NSK-Mar 

As others have stated, Excel does not allow you to paste into a filtered data. The best way to do this is to sort instead of filter. The easiest way to do this, especially for information that is not easily sortable, is to:

- filter your info

- fill the cells of your filtered list with a color

- Clear the filter

- do a custom sort of the list by color and then by a-z (to make it match up with the information you are pasting in)

- Paste your information in your sheet

- Reorder the colors from your cells and reorder the information if necessary.

Note: If the original order of your information is important, do what one user suggested and add a column to your original sheet and number each row, so you can resort back to its original order.

Also, if you are working with extremely large lists where you first have to identify the matching cells before copying over the information, you can always use conditional formatting to identify and highlight any matching cells in a column from one sheet to another within the same workbook. After doing this, you can then follow the steps above to copy the information over.