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

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

  1. Pingback: Best Training on Advanced Excel and VBA in Bangalore | jaseemblog

  2. Digvijay Devidas Khairnar

    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

  3. Arnab Banerjee

    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.


Leave a Reply

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