How to Copy and Paste When Filter Is Applied in Excel

Copy Paste is one of the widely used operations in data management. We can perform copy-paste in Excel comfortably. But when it comes to the filter feature, then becomes complex. The filter is another common feature in Excel. We will discuss how to copy-paste when the filter is applied in Excel. To explain we will use a dataset of a super shop of multiple stores in different cities with the price of some specific fruits.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


5 Methods to Copy and Paste When Filter is Applied in Excel

1. Copy-Paste a Range of Cells Containing Filter

We will copy a range of data from a dataset where the filter is applied. Data will paste on fresh cells where no filter is applied. We will copy the data of the New York store only.

Step 1:

  • Click on the arrow sign of the Store cell.
  • Choose New York from the list.
  • Then, press OK.

Copy-Paste a Range of Cells Containing Filter

See the below image of the dataset.

Copy-Paste a Range of Cells Containing Filter

It contains information about the New York store only.

Step 2:

  • Now, select all the cells of New York.
  • Press Ctrl+C and copy the data.

Step 3:

  • Go to any other cell to paste the data. Here we go to Cell B18.
  • Then press Ctlr+V to paste the data.

Copy-Paste a Range of Cells Containing Filter

Here, we extract the data of the New York store only.


2. Paste a Value to the Required Cells with Filter

We will input one single value with multiple Filtered cells. We can paste on all the cells at a time. The price of the banana changes and will be the same for all the stores. We will sort this here.

Step 1:

  • Click on the filter option of the Fruit cell.
  • Select Banana from the list.
  • Then press OK.

The dataset after applying the filter.

Step 2:

  • The new price of Banana is added on the 17th row.

Paste a Value to the Required Cells with Filter in Excel

Step 3:

  • Copy the new price of Banana from cell C17 by pressing Ctrl+C.
  • We will paste on cells D8, D11, D14, D15. Select those cells.

Step 4:

  • Then press Ctrl+V to paste the data.

Paste a Value to the Required Cells with Filter in Excel

Step 5:

  • Again, go to the Fruit Follow steps 1,2,3 sequentially shown on the image.

Paste a Value to the Required Cells with Filter in Excel

Finally, get the whole dataset by modifying the value of desired cells.

Paste a Value to the Required Cells with Filter in Excel


3. Copy and Paste a Range of Values with Filter Data

Here, we will copy and paste a range of values within filtered data.

A situation comes that due to transport cost rise price of Dallas store is increased. We will copy the updated price and paste this into the main dataset.

Copy Paste a Range of Values with Filter Data

Step 1:

  • Click on the arrow sign of the Store cell.
  • Select Dallas from the list.
  • Then, press OK.

In the following image, we will see the data of the Dallas store only.

Step 2:

  • Now, delete the values from the Price collum.

Step 3:

  • Put the corresponding cell reference of the Orange of Dallas store on Cell D6.

Copy Paste a Range of Values with Filter Data

Step 4:

  • Now, press Enter.

Step 5:

  • Pull the Fill Handle till the last cell containing data.

Copy Paste a Range of Values with Filter Data

Step 6:

  • We want to show the whole dataset now. So, tick Select All from the filter option of Store cell.
  • Then press OK.

We get the whole dataset with the modified price of the Dallas store.

Copy Paste a Range of Values with Filter Data

Step 7:

  • Now, delete the data of Cell E15.

Copy Paste a Range of Values with Filter Data

Here, we can see that Cell D15 is blank. As the reference of that cell is blank.

Step 8:

  • Undo the previous process by pressing Ctrl+Z.
  • Select the cell of the Price column and press Ctrl+C and copy those data.

Step 9:

  • Now, press the right button of the mouse.
  • From the Paste option chooses Values(V).

Copy Paste a Range of Values with Filter Data

This is the dataset after applying copy paste.

Step 10:

  • Now, delete the values of the New Price column.


Similar Readings


4. Use VBA Macro to Copy and Paste with Filtered Cells

We will use VBA Macro to copy and paste data with a filter.

We assume the same condition for applying this method and the dataset is below.

Step 1:

  • Filter the data of the Dallas store only by following the steps of the previous method.

Step 2:

  • Now, go to the Developer tab.
  • Select Record Macro.
  • Create a new Macro named Copy_Paste_Data.
  • Then press OK.

VBA Macro to Copy Paste with Filtered Cells in Excel

Step 3:

  • Go to the Macros option.
  • Select Copy_Paste_Data and then click on Step Into.

VBA Macro to Copy Paste with Filtered Cells in Excel

Step 4:

  • Now, write the below code on the command window.
Sub Copy_Paste_Data()
Dim n As Range
Dim source_data_1 As Range
Dim destination_data_1 As Range
Dim source_data_2 As Range
Dim destination_data_2 As Range
Set n = Application.Selection
n.SpecialCells(xlCellTypeVisible).Select
Set source_data_1 = Application.Selection
Set destination_data_1 = Application.InputBox("Insert the destination Location:", Type:=8)
For Each source_data_2 In source_data_1
source_data_2.Copy
For Each destination_data_2 In destination_data_1
If destination_data_2.EntireRow.RowHeight <> 0 Then
destination_data_2.PasteSpecial
Set destination_data_1 = destination_data_2.Offset(1).Resize(destination_data_1.Rows.Count)
Exit For
End If
Next destination_data_2
Next source_data_2
End Sub

VBA Macro to Copy Paste with Filtered Cells in Excel

Step 5:

  • This code will take input from the selection. So, select the cells that will be paste.

Step 6:

  • Now, run the code by clicking the marked option from the tab. Or press the F5 button.

VBA Macro to Copy Paste with Filtered Cells in Excel

Step 7:

  • Now, a window will appear to input the destination location.

Input the destination location as in the below image.

VBA Macro to Copy Paste with Filtered Cells in Excel

Step 8:

  • Then press OK to see the return.

Here, the value is pasted from the New Price to the Price column.

Step 9:

  • Now, expand the whole dataset by clicking Select All.

The whole dataset is presented below.

Step 10:

  • Now, remove the values from the New Price column.

VBA Macro to Copy Paste with Filtered Cells in Excel

Here, after removing the helping data no changes occurs in the Price column.

Read more: How to Apply Multiple Filters in Excel [Methods + VBA]


5. Additional Quick Access Tool of Filter

We can add a Quick access tool to run a VBA macro code.

Step 1:

  • First click on the Customize Quick Access Toolbar.
  • Select More Commands from the options.

Quick Access Tool of Filter to Copy and Paste

Step 2:

  • Excel Options dialog will appear now.
  • Select the Quick Access Toolbar option.
  • Then select Macros from the Choose commands from the drop-down.

Quick Access Tool of Filter to Copy and Paste

Step 3:

  • Now, add the Copy_Paste_Data.
  • Finally, click OK.

Here, we see the tool on the top of the worksheet.

Quick Access Tool of Filter to Copy and Paste


Additional Notes

If you face any problem while copy-pasting data from filter cell using Ctrl+C, then you need to avail Go to Special. Just follow the required procedures below:

  • Press F5 and the Go To dialog box will appear.
  • Click on Special.

Additional Notes for Copy paste with Filter

  • From the Go To Special choose Visible cells only.
  • Then press OK.

Additional Notes for Copy paste with Filter


Conclusion

In this article, we discussed how to copy-paste when the filter is applied in Excel. I hope this will satisfy your needs. Please have a look at our website Exceldemy.com and give your suggestions in the comment box.


Further Readings

Alok

Alok

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo