We’re going to use a sample dataset as an example. The following dataset represents the Salesman, Product, and Net Sales of a company. There are two products: Cable and TV. We’ll apply the Filter feature to the products.
How to Copy and Paste in Excel When the Filter Is on: 5 Methods
Method 1 – Keyboard Shortcuts to Copy and Paste When the Filter Is on in Excel
Steps:
- Select the range including the Headers.
- Select Filter from the Sort & Filterdrop-down list in the Editing group under the Hometab.
- Select the drop-down symbol beside the header Product.
- Check the Cable box only and press OK.
Case 1.1 – Copy Only the Visible Cells
STEPS:
- Select the range.
- Press Alt + ; to select only the visible cells.
- Press Ctrl + Cto copy.
- Select cell F5 to paste the copied values.
- Press Ctrl + V and Excel will paste the cells.
Case 1.2 – Paste a Value or Formula in the Visible Cells
Steps:
- Select cell F5 as this is the value we want to paste in the filtered column.
- Press Ctrl + C to copy.
- Select the cells in the filtered column where you want to paste the F5 cell value.
- Press F5 or Ctrl + G, and a Go To dialog box will pop out.
- Select Special.
- In the Go To Special dialog box, select Visible cells only and press OK.
- Press Ctrl + V to paste the value.
- If you remove the Filter feature, you’ll see the new value only in the visible cells of the previously filtered column.
Case 1.3 – Paste a Set of Values from Left to Right in a Filtered Table
Steps:
- Select the range.
- Hold the Ctrl key and select the range of cells where you want to paste.
- Press Alt + ;.
- Press Ctrl + R to paste the values in the required column.
Method 2 – Use the Fill Feature for Pasting a Set of Values from Right to Left in a Filtered Table
We applied a keyboard shortcut to paste a set of values from Left to Right in a filtered table. But, there is no such way to do that from Right to Left. We need a different method.
Steps:
- Select the range of cells.
- Hold Ctrl and select the column in the left where you want to paste.
- Press Alt + ; to select only the visible cells.
- Select Left from the Fill drop-down list in the Editing group under the Hometab.
- This will paste the values in the selected column on the left side.
Method 3 – Excel Find & Select Feature to Copy Only the Visible Cells in a Filtered Column
Steps:
- Select the range you want to copy.
- Under the Home tab, select Go To Special from the Find & Select drop-down list in the Editingtab.
- Select Visible cells only.
- Press OK.
- Select Copy in the Clipboardsection.
- Select any cell you where you want to paste.
- Press Ctrl + V.
Method 4 – Apply a Formula to Paste a Set of Values to the Visible Cells
We want to copy the values in column E and paste them into column D only for the product Cable.
Steps:
- Apply a filter to select only rows with Cable for Product.
- Select cell D5 and insert the formula:
=E5
- Press Enter and use the AutoFill tool to fill the series.
- It’ll simply paste the values.
Read More: Copy and Paste Formulas Without Changing Cell References
Method 5 – Paste a Set of Values with Excel VBA When the Filter Is On
STEPS:
- Under the Developer tab, select Visual Basic.
- Under the Insert tab, select Module.
- A new window will pop out.
- Paste in the code given below:
Sub Paste()Dim rg As RangeDim visible_source As RangeDim destination As RangeDim source As RangeDim r As RangeSet rg = Application.Selectionrg.SpecialCells(xlCellTypeVisible).SelectSet visible_source = Application.SelectionSet destination = Application.InputBox("Choose Destination:", Type:=8)For Each source In visible_sourcesource.CopyFor Each r In destinationIf r.EntireRow.RowHeight <> 0 Thenr.PasteSpecialSet destination = r.Offset(1).Resize(destination.Rows.Count)Exit ForEnd IfNext rNext sourceEnd Sub
- Close the Visual Basicwindow.
- Select the range to copy.
- Select Macros under the Developertab.
- A Macro dialog box will open.
- Select Paste in the Macro name and press Run.
- Another dialog box will pop out asking to choose the destination.
- In the Choose Destination box, type $D$5:$D$10 or select the range of cells in the table where you want to paste the values, then press OK.
- The required output will appear in column D.
Read More: Copy and Paste Without Changing the Format in Excel
Download the Practice Workbook
Copy and Paste When Filter is On.xlsm
Related Articles
- Copy and Paste in Excel without Formulas
- Copy Horizontal and Paste Vertical in Excel
- Copy Paste Vertical to Horizontal in Excel
- Copy and Paste a Column in Excel
- Disable Copy and Paste in Excel without Macros
- Copy Formatting in Excel
- Copy and Paste in Excel with Merged Cells
- Copy and Paste in Excel and Keep Cell Size
- Copy Same Value in Multiple Cells in Excel
- Paste Image into Excel Cell from Clipboard
- Copy Formatting in Excel to Another Sheet
- Fill Across Worksheets in Excel
<< Go Back to Copy Paste in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!