How to Copy Rows in Excel with Filter (6 Fast Methods)

Get FREE Advanced Excel Exercises with Solutions!

Often you may need to copy filtered rows in Excel. And this task can be accomplished using Excel as well as the VBA code. In this article, I’ll show you 5 methods on how to copy rows in Excel with a filter with proper explanation.


How to Copy Rows in Excel with Filter: 6 Methods

Let’s be introduced with today’s dataset (B1:F16 cell range) as shown in the following screenshot. Here, the Number of Visits for each website is provided along with the Name and Category of the Sites. Besides, the dates and mode of Platforms are also given. Now, we need to copy rows keeping filters.

Dataset

For example, you want to filter the dataset based on Education, a category of the sites.

For doing this, select the Filter option from the Data tab.

How to Filter Data

Then, check the box before Education and uncheck the rest boxes.

How to Filter Data

After pressing OK, you’ll get the following filtered rows.

How to Filter Data

Whatever, let’s come to our topic. We need to copy this type of filtered rows quickly in Excel.


1. Using the Go To Special Option

In the beginning method, I’ll show you a simple tool to copy filtered rows. And that is the application of the Go To Special option actually.

To use the option, you have to select the range of filtered rows (B4:B16).

Next, move the cursor on the Find & Select option in the Editing ribbon of the Home tab.

After clicking on the Find & Select option, you’ll see the Go To Special option and choose the option.

Using the Go To Special Option

Then, you’ll see a dialog box where you need to check the circle before the Visible cells only option.

Using the Go To Special Option

After pressing OK, you have to copy the range of filtered rows (e.g. you may copy using Ctrl + C).

Finally, you need to paste the filtered data by pressing Ctrl + V or choosing the Paste Option from the Context Menu (by right-clicking).

Using the Go To Special Option

In the above picture, you see that the filtered data are copied without any hidden rows.

Note: In the above picture, I copied the filtered rows in the existing working sheet. However, you may accomplish the task in the new working sheet in the same manner.


2. Copy Rows with Excel Filter Using Keyboard Shortcut

If you prefer using the keyboard shortcut, this method will be beneficial for you. Luckily, you don’t need any option or tool to copy filtered rows if you know the shortcuts.

To apply the shortcuts, you have to select the filtered rows then press Alt + ; for selecting the visible cells only.

Later, press Ctrl + C to copy the visible cells.

Ultimately, you need to press Ctrl + V in a new destination.

Thus, you’ll get the same filtered rows as shown in the following screenshot.

how to copy rows in excel with filter using keyboard shortcut

Note: In maximum cases, you can copy the filtered rows by pressing simply Ctrl + C to copy and Ctrl + V to paste. In other words, you don’t need to press Alt + ;.


3. Apply Excel Formula to Copy Rows with Filter

In the first picture, you may find an unexpected issue.

If you look closely at Column F, you’ll get that all filtered rows of Column D are not copied exactly.

Because there exist hidden rows.

Apply Excel Formula to Copy Rows with Filter

So, how can you overcome the dilemma?

Truly, it’s a very easy task. Just insert the following formula in the F5 Cell.

=D5

Here, D5 is the starting cell of the number of visits.

Then, press Ctlr + Enter or use the Fill Handle tool after pressing the Enter button.

Immediately, you’ll get all filtered rows of Column D.

Apply Excel Formula to Copy Rows with Filter


4. Copy Filtered Rows Using a Command from the Quick Access Toolbar (QAT)

Probably, you are accustomed to using one of the productive features of Excel i.e. Quick Access Toolbar. You might see the toolbar (yellow-colored in the following picture) in Excel.

how to copy rows in excel with filter Quick Access Toolbar

Luckily, you can customize the toolbar to copy filtered rows.

For customizing it, you have to click on the toolbar first.

Immediately, you’ll see many options and choose More Commands.

how to copy rows in excel with filter Quick Access Toolbar

Next, you’ll find a dialog box where you have to select the option sequentially i.e. All Commands > Select Visible Cells > Add > OK.

how to copy rows in excel with filter Quick Access Toolbar

Eventually, you’ll get the command in the Quick Access Toolbar. Now, click on the command, then press Ctrl + C and use Ctrl + V for pasting the data in a new destination.

how to copy rows in excel with filter Quick Access Toolbar

Read More: Copy Rows from One Sheet to Another Based on Criteria


5. Copy Filtered Rows to New Worksheet Automatically 

In the above-discussed 4 methods, you learned how to copy rows in Excel with filters in the existing working sheet. Fortunately, you may accomplish the same task in a new working sheet. But you have to open the new working sheet manually.

How would you feel if the new worksheet opens automatically with the filtered rows?

Obviously, that will be effective and time-saving.

Let’s explore the method.

Advanced Filter tool is really sophisticated to filter the dataset as you can specify any criteria with the filtering.

Whatever, I fixed the criteria range located at the C19:D20 range. That means I want to filter the dataset that covers either the Education category or Web platforms.

how to copy rows in excel with filter to new sheet automatically

While doing this method, you have to be careful at this stage.

When you are going to turn the Advanced filter from the Sort & Filter ribbon in the Home tab, make sure that your active sheet is the new working sheet (e.g. New Sheet1 is the new working sheet in the practice workbook).

how to copy rows in excel with filter to new sheet automatically

Then, you’ll see a dialog box namely Advanced Filter. Now, follow the below things.

Firstly, check the circle before the Copy to another location option.

Later fix the ‘Advanced Filter’!$B$4:$F$16 as the List range (here the name of the existing worksheet is Advanced Filter).

Meanwhile, you have to specify the ‘Advanced Filter’!Criteria as the Criteria range.

how to copy rows in excel with filter to new sheet automatically

Subsequently, you have to appoint the location ‘New Sheet1’!$B$4:$F$4 after the Copy to option.

how to copy rows in excel with filter to new sheet automatically

Finally, press OK and you’ll get the filtered rows in the new working sheet (New Sheet1).

how to copy rows in excel with filter to new sheet automatically

Read More: Copy Every Nth Row in Excel


6. Using VBA Code to Copy Rows in the New Sheet

Again, if you wish to use the VBA code in Excel to copy rows with filters, you may follow this method.

For example, I want to copy the value of Number of Visits located at the F4:F16 cell range.

Using VBA Code to copy rows with Excel filter

To execute the task, follow the below 3 steps.

Step 01: Inserting a Module

Firstly, open a module by clicking Developer > Visual Basic.

How to Insert VBA Code

Secondly, go to Insert > Module.

How to Insert VBA Code

Step 02: Copying the VBA Code

Now, copy the following code in the newly created module.

Sub Copy_FilteredRows_NewSheet()
Dim Rng As Range
Dim Selecting_FilteredRows As Range
Dim destination As Range
Dim source As Range
Dim r As Range
Set Rng = Application.Selection
Rng.SpecialCells(xlCellTypeVisible).Select
Set Selecting_FilteredRows = Application.Selection
Set destination = Application.InputBox("Please Select the Output Range:", Type:=8)
For Each source In Selecting_FilteredRows
source.Copy
For Each r In destination
If r.EntireRow.RowHeight <> 0 Then
r.PasteSpecial
Set destination = r.Offset(1).Resize(destination.Rows.Count)
Exit For
End If
Next r
Next source
End Sub

Using VBA Code

In the above code,

i. At the outset, I declared some variables e.g. Rng, Selecting_FilteredRows, etc.

ii. Later, I used the Selection property to return the objects to the active sheet.

iii. Immediately, I used SpecialCells to specify the cells and fixed xlCellTypeVisible as the cell type (the first argument) of the Special Cells.

iv. Again, I utilized the Selection property to select the Select_FilteredRows.

v. Then I appointed an InputBox to select the output range.

vi. Thereafter, I employed For Loop to copy the selected filtered rows.

vii. In addition, I assigned the If logical statement to get the value for skipping hidden rows.

viii. Lastly, Offset is used to return in a specified input range and Resized property is applied to get the resized output.

Step 03: Running the VBA Code

If you run the code (the keyboard shortcut is F5 or Fn + F5), you’ll get a dialog box first.

So, fix the output range where you need to copy the rows. Here, I appointed ‘New Sheet2’!$B$4:$B$11 as the output range (New Sheet2 is the name of the new working sheet).

Using VBA Code

When you press OK after selecting the output range, you’ll get the filtered rows.

Using VBA Code


Download Practice Workbook


Conclusion

This is how you can copy rows keeping filters in Excel. I strongly believe this article will be highly beneficial for you. Anyway, if you have any queries or recommendations, please share them in the comments section.


Related Articles


<< Go Back to Copy Rows | Copy Paste in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Abdul Kader
Md. Abdul Kader

MD. ABDUL KADER is an engineer with a talent for Excel and a passion for VBA programming. To him, programming is like a time-saving wizard, making data manipulation, file handling, and internet interactions a breeze. His skill set extends to SWM, GIS, RS, and SPSS. He holds a B.Sc in Urban & Regional Planning from Chittagong University of Engineering and Technology and has shifted to become a content developer. In this role, he crafts technical content centred around... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo