Learn How to Copy Filtered Data to Another Workbook using VBA in Excel

VBA stands for Visual Basic for Applications, which is a programming language that allows you to automate tasks in Excel and other Office applications. VBA can be used to write macros, which are sequences of commands that perform specific actions.

One of the common tasks that you may want to automate with VBA is copying filtered data from one worksheet to another workbook. This can be useful when you have a large dataset and you want to extract only the relevant information based on some criteria.

To copy filtered data from one worksheet to another workbook, you need to use the AdvancedFilter method of the Range object. This method allows you to apply a filter to a range of cells and copy the filtered data to another location. The syntax of the AdvancedFilter method is:

Range.AdvancedFilter(Action, CriteriaRange, CopyToRange, Unique)

The parameters of the method are:

• Action: This specifies whether to filter the data in place or copy it to another location. You can use the constants xlFilterInPlace or xlFilterCopy for this parameter.
• CriteriaRange: This is the range of cells that contains the criteria for the filter. The criteria range must have the same number of columns as the range to filter, and the column headers must match exactly.
• CopyToRange: This is the range of cells where the filtered data will be copied. This parameter is required only if the Action parameter is set to xlFilterCopy. The copy range must have the same number of columns as the range to filter, and the column headers must match exactly.
• Unique: This is an optional parameter that specifies whether to copy only the unique values from the filtered data. You can use the constants True or False for this parameter. The default value is False.

Procedures

To copy filtered data from one worksheet to another workbook using VBA, you need to follow these steps:

1. Define the range to filter, the criteria range, and the copy range as Range objects. You can use the Sheets and Range properties to refer to the worksheets and cells in different workbooks.
2. Use the AdvancedFilter method of the range to filter, and pass the criteria range, the copy range, and the xlFilterCopy constant as arguments.
3. Optionally, you can use the Unique parameter to copy only the unique values from the filtered data.

Explanation

To illustrate how to copy filtered data from one worksheet to another workbook using VBA, let’s use an example scenario with real numbers.

Suppose you have a workbook named “Sales.xlsx” that contains a worksheet named “Data”. The worksheet has the following data in range A1:E11:

You want to copy the data where the product is A and the quantity is greater than 50 to another workbook named “Filtered.xlsx” that contains a worksheet named “Result”. The worksheet has the following headers in range A1:E1:

| Order ID | Product | Quantity | Price | Region |

To copy the filtered data from “Sales.xlsx” to “Filtered.xlsx” using VBA, you can use the following code:

Sub CopyFilteredData()

'Declare the variables
Dim wsData As Worksheet 'The worksheet with the data to filter
Dim wsResult As Worksheet 'The worksheet where the filtered data will be copied
Dim rngData As Range 'The range to filter
Dim rngCriteria As Range 'The range with the criteria for the filter
Dim rngCopy As Range 'The range where the filtered data will be copied

'Set the variables
Set wsData = Workbooks("Sales.xlsx").Sheets("Data") 'Change the workbook and worksheet names as needed
Set wsResult = Workbooks("Filtered.xlsx").Sheets("Result") 'Change the workbook and worksheet names as needed
Set rngData = wsData.Range("A1:E11") 'Change the range address as needed
Set rngCriteria = wsData.Range("G1:H2") 'Change the range address as needed
Set rngCopy = wsResult.Range("A1:E1") 'Change the range address as needed

'Copy the filtered data to the destination
rngData.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=rngCriteria, CopyToRange:=rngCopy, Unique:=False

End Sub

The code does the following:

• It declares and sets the variables for the worksheets and ranges involved in the filtering and copying process.
• It uses the AdvancedFilter method of the range to filter, and passes the criteria range, the copy range, and the xlFilterCopy constant as arguments. It also sets the Unique parameter to False to copy all the matching values from the filtered data.
• The criteria range (G1:H2) has the following values:

This means that the filter will only copy the rows where the product is A and the quantity is greater than 50.

After running the code, the filtered data will be copied to the “Result” worksheet in “Filtered.xlsx”. The worksheet will have the following data in range A1:E4:

Other approaches

There are other ways to copy filtered data from one worksheet to another workbook using VBA. Here are some of them:

• You can use the AutoFilter method of the Range object to apply a filter to a range of cells, and then use the SpecialCells method with the xlCellTypeVisible constant to copy only the visible cells to another location. This is similar to the AdvancedFilter method, but it allows you to use more complex criteria and operators for the filter.
• You can use the Copy and PasteSpecial methods of the Range object to copy and paste the filtered data to another location. You can use the xlPasteValues and xlPasteFormats constants to paste only the values and formats of the filtered data, without the formulas. This is useful if you want to preserve the formatting of the filtered data, or if you want to avoid any errors or references in the formulas.
• You can use a loop to iterate through each cell in the range to filter, and check if it meets the criteria for the filter. If it does, you can use the Offset property of the Range object to copy the values from the corresponding columns to another location. This is useful if you want to have more control over the copying process, or if you want to copy only specific columns from the filtered data.