How to Autofilter and Copy Visible Rows with Excel VBA

The dataset below has 5 columns displaying a Site’s name, Category, Date, Platform, and Number of Visits for each site.

Method 1 – AutoFilter and Copy Visible Rows in Existing Sheet Using Excel VBA

Steps:

Open a module by clicking Developer > Visual Basic.

Go to Insert > Module.

Copy the following code:

Sub Copy_AutoFiltered_VisibleRows()
'Declares CatSites i.e. Category of the Sites
Dim CatSites As String
'Education is the filtering criteria
CatSites = "Education"
'AutoFilter for a specific category of the sites which is in Column B
ActiveSheet.Range("A1:E14").AutoFilter field:=2, Criteria1:=CatSites
'Copy only visible cells
ActiveSheet.Range("A1:E14").SpecialCells(xlCellTypeVisible).Copy
ActiveSheet.Range("G1").PasteSpecial Paste:=xlPasteAll
Application.CutCopyMode = False
'Remove AutoFilter
ActiveSheet.AutoFilterMode = False
End Sub

Run the VBA Code

You’ll get the following filtered and copied visible rows when you run the code (the keyboard shortcut is F5 or Fn + F5).

Method 2 – Copy Auto-Filtered Visible Rows in a New Sheet

To get the filtered values in a new working sheet.

Use the following code in a newly created module in such a situation:

Sub Copy_AutoFiltered_VisibleRows_NewSheet()
'Declares CatSites i.e. Category of the Sites
Dim CatSites As String
'Education is the filtering criteria
CatSites = "Education"
'AutoFilter for a specific category of the sites which is in Column B
ActiveSheet.Range("A1:E14").AutoFilter
ActiveSheet.Range("A1:E14").AutoFilter field:=2, Criteria1:=CatSites
'Copy only visible cells
ActiveSheet.Range("A1:E14").SpecialCells(xlCellTypeVisible).Copy
Worksheets("Sheet3").Range("A1").PasteSpecial Paste:=xlPasteAll
Application.CutCopyMode = False
'Remove AutoFilter
ActiveSheet.AutoFilterMode = False
End Sub

This code is almost the same as the code for getting the output in the existing sheet. The only exception (yellow-colored in the above screenshot) is that you have to use the Worksheet Function to appoint the new working sheet (Sheet3 in the case of the practice workbook). Besides, the Range(“A1”) specifies that A1 will be the starting cell.

To run the code follow the steps:

First, open the sheet from where you want to copy the filtered values. Suppose you selected Dataset sheet.
Then, go to Developer tab >> from Macros >> select Sub Copy_AutoFiltered_VisibleRows_NewSheet().

Now, you will get the filtered values in Sheet 3. Make sure you have created a sheet named Sheet3.
If you run the macro after copying the code, you’ll get the following output in the new Working Sheet3.

Method 3 – AutoFilter Using Input Box and Copy Filtered Visible Rows

If you want to utilize the InputBox to enter a specific value, utilize the third method.

Use InputBox to enter a mode of the platforms. The macro will auto-filter the dataset based on the Web, and then it’ll return the copied visible cells only.

Sub Copy_AutoFiltered_VisibleRows_InputBox()
'Declares PlatformsMode i.e. the Mode of Platforms
Dim PlatformsMode As String
'Using InputBox to enter value
PlatformsMode = InputBox("Enter a mode of platforms")
'AutoFilter for a specific mode of platforms which is in Column D
ActiveSheet.Range("A1:E14").AutoFilter
ActiveSheet.Range("A1:E14").AutoFilter field:=4, Criteria1:=PlatformsMode
'Copy only visible cells
ActiveSheet.Range("A1:E14").SpecialCells(xlCellTypeVisible).Copy
Worksheets("Sheet5").Range("A1").PasteSpecial Paste:=xlPasteAll
Application.CutCopyMode = False
'Remove AutoFilter
ActiveSheet.AutoFilterMode = False
End Sub

In this VBA code:

i. I declared the PlatformsMode, i.e., Platforms in the dataset, as the String data type.

ii. I assigned InputBox to the PlatformsMode for entering a mode of the platforms.

iii. I changed the field of the AutoFilter to 4 (Platforms located in Column D) and Criteria1 as PlatformsMode.

iv. I specified Sheet5 (new working sheet) with the Worksheet function.

After running the code, you’ll get a dialog box where you need to input a mode of platforms.

You will get the following output after pressing OK.

Quick Notes

i. Checking Whether Auto Filter is Applied or Not

When you have to apply an AutoFilter, you may be unclear about whether the auto filter is applied or not in the active working sheet. Use the following code to check.

Sub Checking_AutoFilter()
If ActiveSheet.AutoFilterMode = True Then
MsgBox "Auto Filter is Applied"
Else
MsgBox "Auto Filter is Applied"
End If
End Sub

ii. Display All Filtered Data

You may need to retrieve all data after filtering. In that case, you may utilize the following code to display all data.

Sub Display_Data()
If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData
End Sub

Download the Practice Workbook

AutoFilter and Copy Visible Rows.xlsm

Get FREE Advanced Excel Exercises with Solutions!

Sub Copy_AutoFiltered_VisibleRows_NewSheet() 'Declares CatSites i.e. Category of the Sites Dim CatSites As String 'Education is the filtering criteria CatSites = "Education" 'AutoFilter for a specific category of the sites which is in Column B ActiveSheet.Range("A1:E14").AutoFilter ActiveSheet.Range("A1:E14").AutoFilter field:=2, Criteria1:=CatSites 'Copy only visible cells ActiveSheet.Range("A1:E14").SpecialCells(xlCellTypeVisible).Copy 'Provide your sheet name instead of Sheet3 or create a sheet name it Sheet3. Worksheets("Sheet3").Range("A1").PasteSpecial Paste:=xlPasteAll Application.CutCopyMode = False 'Remove AutoFilter ActiveSheet.AutoFilterMode = False End Sub

6 Comments

Malin Deka Oct 3, 2022 at 6:26 PM

Your thinking and attitude are charming and helpful to a learning person. Go ahead.
- Reply
  Md. Abdul Kader Oct 4, 2022 at 11:26 AM
  
  Dear Deka,
  It’s nice to hear from you with such wonderful appreciation. I myself always try to represent Excel applications in a more user-friendly way. And we, the ExcelDemy team, are working as a one-stop Excel solution provider.
  Thanks, and take care of yourself. Goodbye!
  Md. Abdul Kader
  Editor, ExcelDemy.
  - Reply
    fredrick aringo Apr 16, 2024 at 4:08 AM
    
    Thanks for a well explained scripts on using autofilter. The only thing I am not getting properly is the two vba lines below. Is it possible to just use the last one to avoid the repetation.
    ActiveSheet.Range(“A1:E14”).AutoFilter
    ActiveSheet.Range(“A1:E14”).AutoFilter field:=2, Criteria1:=CatSites
    - Shamima Sultana Apr 16, 2024 at 11:28 AM
      
      Hello Fredrick Aringo,
      
      You must use the last line to do AutoFilter based on criteria.
      
      ActiveSheet.Range(“A1:E14”).AutoFilter field:=2, Criteria1:=CatSites
      
      Please avoid the first line. We updated our code.
      
      Regards
      ExcelDemy
Iliyana Aug 8, 2024 at 3:09 PM

Hi,

I am trying to run the filter-copy-paste in new sheet.
But I keep getting that there is an error in the following comand row:

Worksheets(“Sheet3”).Range(“a1”).PasteSpecial Paste:=xlPasteAll

I really don’t know to do.
I’ve already re-write it by hand, copied from the example, run the example it self. The yellow line i still there.

Help. How do I get it work?
- Reply
  Shamima Sultana Aug 9, 2024 at 12:46 PM
  Hello Iliyana,
  
  Hope you are doing well. Sorry to hear your problem. While executing the code you need to follow the steps.
  First, open the sheet from where you want to copy the filtered values. Suppose you selected Dataset sheet.
  Go to Developer tab >> from Macros >> select Sub Copy_AutoFiltered_VisibleRows_NewSheet().
  Now, you will get the filtered values in Sheet3. Make sure you have created a sheet named Sheet3 otherwise you will get the error out of “Subscript out of range” and it will show a yellow fill in Worksheets(“Sheet3”).Range(“a1”).PasteSpecial Paste:=xlPasteAll line.
  
  If you want to get your filtered values in your desired sheet. Just name it in the code.
```
Sub Copy_AutoFiltered_VisibleRows_NewSheet()
'Declares CatSites i.e. Category of the Sites
Dim CatSites As String
'Education is the filtering criteria
CatSites = "Education"
'AutoFilter for a specific category of the sites which is in Column B
ActiveSheet.Range("A1:E14").AutoFilter
ActiveSheet.Range("A1:E14").AutoFilter field:=2, Criteria1:=CatSites
'Copy only visible cells
ActiveSheet.Range("A1:E14").SpecialCells(xlCellTypeVisible).Copy
'Provide your sheet name instead of Sheet3 or create a sheet name it Sheet3.
Worksheets("Sheet3").Range("A1").PasteSpecial Paste:=xlPasteAll
Application.CutCopyMode = False
'Remove AutoFilter
ActiveSheet.AutoFilterMode = False
End Sub
```
  If you still find the issue please attach a image or error name and it’s description. We will be happy to help you.
  
  Regards
  ExcelDemy