Extract Data from one Excel Worksheet to another using Advanced Filter with VBA

We know how to use advanced filter to extract data from our raw data in the same worksheet. How do we extract data from one Excel worksheet to another using advanced filter with VBA?

  1. Enter data in Sheet1
  2. Create a criteria range
  3. Copy the required headers from Sheet1 where your data is entered to another worksheet, let’s say Sheet2
  4. Click away from the headers in Sheet2
  5. Click on the Data tab
  6. From the command group Sort & Filter select the Avanced Filter option
  7. Go to the data in Sheet1 for example
  8. Select the data range with the headers to define the ListRange in the Advanced Filter window
  9. Select the criteria range to define the criteria range in the advanced filter window
  10. Click on Copy to Another Location option button in the advanced filter window
  11. Now go back to Sheet 2 and select the headers so that they appear in the text-box next ‘Copy to:’
  12. Click on the OK button and you get the filtered data in your worksheet 2

Now based on these steps that we have used to do the filtering manually, we write our VBA code attached to a command button. The complete VBA code is given below:

Private Sub CommandButton1_Click()
Application.CutCopyMode = False
Sheets(“Sheet1”).Range(“A1:G9”).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Sheets(“Sheet1”).Range(“I1:I2”), CopyToRange:=Sheets(“Sheet4”).Range(“A1:B1”), Unique:=False

End Sub
Watch the video:

Watch the video on YouTube

Further reading:
Extract data using Advanced Filter and VBA

11 thoughts on “Extract Data from one Excel Worksheet to another using Advanced Filter with VBA

    1. I receive a data message reporting you can only filter data in the active sheet?

      It doesn’t matter if I am in worksheet1 or worksheet2. The same message appears

  1. Hello its very good source for learning VBA thanks a lot for sharing the information and teaching the VBA.
    I have a requirement where I have data like in Index and Group
    Index Group No
    Index 1, Group 1
    Index 2,Group 1
    Index 3,Group 1
    Index 1,Group 2
    Index 2,Group 2
    Index 3,Group 2
    What I want in result in another sheet is
    Heading as Group 1 and all values all Indices with matching Group 1 from above and so on i.e.
    finally I want is
    Group 1 Group 2
    Index 1 Index 1
    Index 2 Index 2
    Index 3 Index 3

    Without VB will be appriciated

  2. Hi Sir
    I have a workbook with inventory of 5 location in 5 sheets(toal line are items are >50),now I want to filter and find sum of the stock of 10 inventory items in sheet 6 using VBA,need your advice in this case.

  3. I have an excel file in which multiple sheets are present. From “Sheet1”, I have to look for a character “d” in column “M”. If “d” is present in column “M”, then I have to copy the row contents (cells A-H) where the character is found to a new sheet “Sheet2” and send the reminder to mail address with the contents of “Sheet2” through Microsoft Outlook´╗┐

  4. Sub test()

    Dim lookupTable As Range
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets(“Sheet3”) ‘change as appropriate
    Set lookupTable = ws.Range(“A2:C7”)

    Dim dueDate As Date

    dueDate = ws.Range(“B15”).Value2

    MsgBox GetPayPeriod(dueDate, lookupTable)

    End Sub

    Public Function GetPayPeriod(ByVal dueDate As Date, ByVal lookupTable As Range) As String

    If dueDate = payPeriodsArray(i, 1) And dueDate <= payPeriodsArray(i, 2) Then

    GetPayPeriod = payPeriodsArray(i, 3)
    Exit Function

    End If

    Next i

    GetPayPeriod = "Period not found"

    End Function

  5. How would you check multiple columns for criteria? for example, if any of the mark counts were above 85 then it would be included in sheet 2. thanks

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.