Alok
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.
See the below image of the dataset.
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.
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.
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.
Step 5:
- Again, go to the Fruit Follow steps 1,2,3 sequentially shown on the image.
Finally, get the whole dataset by modifying the value of desired cells.
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.
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.
Step 4:
- Now, press Enter.
Step 5:
- Pull the Fill Handle till the last cell containing 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.
Step 7:
- Now, delete the data of Cell E15.
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).
This is the dataset after applying copy paste.
Step 10:
- Now, delete the values of the New Price column.
Similar Readings
- How to Add Filter in Excel (4 Methods)
- Shortcut for Excel Filter (3 Quick Uses with Examples)
- Filter Multiple Columns Simultaneously in Excel (3 Ways)
- How to Filter Unique Values in Excel (8 Easy Ways)
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.
Step 3:
- Go to the Macros option.
- Select Copy_Paste_Data and then click on Step Into.
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
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.
Step 7:
- Now, a window will appear to input the destination location.
Input the destination location as in the below image.
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.
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.
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.
Step 3:
- Now, add the Copy_Paste_Data.
- Finally, click OK.
Here, we see the tool on the top of the worksheet.
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.
- From the Go To Special choose Visible cells only.
- Then press OK.
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.
