Copy only Filtered / Filter View Rows

Question

I have two sheets, Contacts, and Mailer

The visible cells from Contacts : Column B after filtering/filter view on the Google Spreadsheet needs to be copied to the column A of Mailer.

Since there is no open function yet for getvisibility of row, how do I do this? I dont want to write many FILTER() codes because the filters can keep changing, they will be user defined...the results will always be in Column B.

Please let me know of solutions/workarounds.

Worst Case Scenario: until the row visibility function is opened we'll have to manually copy the cells from one sheet to another, but I'm hoping to avoid that

Answer 1

You don't need to switch to Excel. Just select and copy the data from the filtered page, go to the new page, select the cell where you want the copied data, right-click in the cell and select 'Paste Special' > 'Paste Values Only'.

Answer 2

I had the same question and here is what I finally did. If you copy the filtered data you can paste it into Excel and only the visible cells will paste. I then copied that data and pasted it into my Google sheet. We shouldn't have to do this but it's not a bad workaround.

Cheers!

Answer 3

When opening a new tab, when pasting, only the filtered rows get pasted, no need for 'Paste Values Only'. Of course I 'copy' the filtered span first.

Answer 4

As of 4/2021 I tried all of the already mentioned options:

• 'Paste Special' -> 'Paste Values Only'.
• Paste in a new tab
• Paste in excel as a buffer and then copy/paste back to sheets.

None of these worked for me.

---

Here's the workaround I have for rows that are filtered and columns that are hidden/grouped:

Do all of your filtering, hiding, grouping.

If you control/command-click to select all of your visible columns. When you copy/paste it will only paste visible cells (rows and columns).

If you want to keep all the columns in their current grouped/hidden state you can shift-click to select the columns. This method will get rid of filtered rows.