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



     Webhosting by Godaddy     MS Excel Books
Home More Excel (VBA) Training Videos





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()
Sheet1.Activate
Sheet1.Range("A1,G1").Select
Selection.Copy
Sheets("Sheet4").Select
Range("A1:B1").PasteSpecial
Columns("A:A").EntireColumn.AutoFit
Columns("B:B").EntireColumn.AutoFit
Range("C4").Select
Sheets("Sheet1").Select
Application.CutCopyMode = False
Sheet1.Range("A3").Select
Sheets("Sheet4").Select
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:





Extract data from one Excel worksheet to another using advanced filter with VBA

Watch the video on YouTube

References
Extract data using Advanced Filter and VBA
Home More Excel (VBA) Training Videos