How to Use VBA to Filter and Transfer Data Between Workbooks in Excel

One of the advantages of using VBA is that you can manipulate data from different workbooks and worksheets, which are the files and tabs that contain your data in Excel. You can use VBA to open, close, save, activate, or copy data from one workbook or worksheet to another. To do this, you need to use objects, properties, and methods, which are the building blocks of VBA code. An object is anything that you can manipulate in Excel, such as a workbook, a worksheet, a range, or a cell. A property is an attribute of an object, such as its name, value, or color. A method is an action that you can perform on an object, such as opening, closing, copying, or pasting.

In this article, you will learn how to use VBA to filter data from one workbook, then paste the results into another workbook, and repeat the process for multiple worksheets. This can be useful if you have a large dataset that you want to split into smaller subsets based on some criteria, and then transfer the subsets to a different workbook for further analysis or reporting.

Procedures

To accomplish this task, you will need to follow these steps:

1. Define the variables and constants that you will use in your code, such as the names and paths of the source and destination workbooks, the number of worksheets that you want to filter and copy, and the criteria that you want to apply to the filter.
2. Open the source workbook and loop through the worksheets that you want to filter and copy. For each worksheet, apply the filter to the data range using the AutoFilter method, and copy the visible cells using the SpecialCells method.
3. Activate the destination workbook and paste the copied data into the corresponding worksheet using the PasteSpecial method. You can also format the pasted data as you wish, such as adjusting the column width, adding headers, or applying styles.
4. Clear the filter and the clipboard in the source workbook, and move to the next worksheet in the loop. Repeat steps 2 and 3 until all the worksheets are processed.
5. Close the source workbook and save the destination workbook.

Explanation

To illustrate the code and the logic behind it, let’s use a hypothetical scenario. Suppose you have a master workbook named “Sales Data.xlsx” that contains 12 worksheets, one for each month of the year. Each worksheet has the same data structure, with columns A to H containing the following information: Order ID, Customer ID, Product ID, Product Name, Quantity, Unit Price, Discount, and Total. The data range starts from row 2, and the last row varies depending on the number of orders in each month. You want to filter the data based on the product name, and copy the orders for each product to a different workbook named “Product Sales.xlsx”. This workbook has 10 worksheets, one for each product that you sell. The product names are: Bike, Book, Camera, Laptop, Mouse, Pen, Phone, Printer, Tablet, and TV. You want to paste the orders for each product in the same order as they appear in the master workbook, starting from row 2 in each worksheet. You also want to add a header row in each worksheet with the column names, and adjust the column width to fit the data.

Here is the VBA code that can perform this task:

Sub FilterAndCopyData()

'Declare the variables and constants
Dim sourceBook As Workbook 'The source workbook object
Dim destBook As Workbook 'The destination workbook object
Dim sourceSheet As Worksheet 'The source worksheet object
Dim destSheet As Worksheet 'The destination worksheet object
Dim sourceRange As Range 'The source data range object
Dim destRange As Range 'The destination data range object
Dim lastRow As Long 'The last row of the source data range
Dim i As Integer 'The loop counter for the worksheets
Dim productName As String 'The product name to filter by
Dim sourcePath As String 'The path of the source workbook
Dim destPath As String 'The path of the destination workbook
Const numSheets As Integer = 10 'The number of worksheets to filter and copy
Const headerRow As String = "Order ID,Customer ID,Product ID,Product Name,Quantity,Unit Price,Discount,Total" 'The header row for the destination worksheets

'Set the paths of the workbooks
sourcePath = "C:\Users\user\Desktop\Sales Data.xlsx"
destPath = "C:\Users\user\Desktop\Product Sales.xlsx"

'Open the source workbook
Set sourceBook = Workbooks.Open(sourcePath)

'Loop through the worksheets to filter and copy
For i = 1 To numSheets

    'Set the source worksheet object
    Set sourceSheet = sourceBook.Worksheets(i)
    
    'Set the product name to filter by
    productName = destBook.Worksheets(i).Name
    
    'Set the last row of the source data range
    lastRow = sourceSheet.Cells(Rows.Count, 1).End(xlUp).Row
    
    'Set the source data range object
    Set sourceRange = sourceSheet.Range("A2:H" & lastRow)
    
    'Apply the filter to the source data range
    sourceRange.AutoFilter Field:=4, Criteria1:=productName
    
    'Copy the visible cells from the source data range
    sourceRange.SpecialCells(xlCellTypeVisible).Copy
    
    'Activate the destination workbook
    destBook.Activate
    
    'Set the destination worksheet object
    Set destSheet = destBook.Worksheets(i)
    
    'Set the destination data range object
    Set destRange = destSheet.Range("A2")
    
    'Paste the copied data into the destination data range
    destRange.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    
    'Clear the filter and the clipboard in the source workbook
    sourceBook.Activate
    sourceSheet.ShowAllData
    Application.CutCopyMode = False
    
Next i

'Close the source workbook
sourceBook.Close SaveChanges:=False

'Format the destination workbook
destBook.Activate
For i = 1 To numSheets
    'Set the destination worksheet object
    Set destSheet = destBook.Worksheets(i)
    'Add the header row
    destSheet.Range("A1").Value = headerRow
    'Adjust the column width
    destSheet.Columns("A:H").AutoFit
Next i

'Save the destination workbook
destBook.Save

End Sub

Other approaches

Using VBA is one way to filter data from one workbook and paste it into another, but it is not the only way. Depending on your situation and preference, you may want to consider other approaches, such as:

• Using Power Query: Power Query is a feature in Excel that allows you to connect to, transform, and combine data from different sources, such as other workbooks, databases, web pages, or text files. You can use Power Query to import data from the source workbook, apply filters, and load the results into the destination workbook. You can also refresh the data with a click of a button, without having to run the VBA code again. To learn more about Power Query, you can visit this link.
• Using formulas: You can use formulas in Excel to extract data from one workbook and display it in another, based on some criteria. For example, you can use the VLOOKUP function to look up the product name in the source workbook, and return the corresponding order details in the destination workbook. However, this method may not be efficient or scalable if you have a large amount of data or multiple criteria to filter by. To learn more about formulas, you can visit this link.
• Using advanced filter: You can use the advanced filter feature in Excel to filter data from one workbook and copy it to another, without using VBA. You can specify the criteria range, the data range, and the copy-to range, and Excel will do the rest. However, this method may not be convenient or flexible if you have to repeat the process for multiple worksheets or change the criteria frequently. To learn more about advanced filter, you can visit this link.