Vba copy filtered data in Excel

For example, I want to filter records pertaining to only Barbara and paste them in a table starting from cell AT4.

excel vba copy filtered data

To do it in Excel, here is the answer:

  1. Option Explicit
  2. Sub CopyFilteredData()
  3. Dim sName As String
  4. ''
  5. ''
  6. sName = "Barbara"
  7. ''
  8. 'Filter rows based on Name which is Field 2 (Col AQ).
  9. ActiveSheet.Range("AP4:AR4").AutoFilter
  10. ActiveSheet.Range("AP4:AR14").AutoFilter Field:=2, Criteria1:=sName
  11. ''
  12. 'Copy filtered table and paste it in Destination cell.
  13. ActiveSheet.Range("AP4:AR14").SpecialCells(xlCellTypeVisible).Copy
  14. ActiveSheet.Range("AT4").PasteSpecial Paste:=xlPasteAll
  15. Application.CutCopyMode = False
  16. ''
  17. 'Remove filter that was applied.
  18. ActiveSheet.AutoFilterMode = False
  19. ''
  20. End Sub

Description:

a) Line 13 - after applying filters, use of Specialcells(xlCellTypeVisible) copies only visible (filtered) cells.

Result after Macro execution:

excel vba copy filtered data

 

You can find similar Excel Questions and Answer hereunder

1) How to change desktop background in Excel

2) How can I use SUMPRODUCT to summarize my raw data?

3) How can I get row count of filtered data?

4) How can I hide Formula Bar and Headings using VBA?

5) How can I add a Timestamp after macro execution?

6) Vba clear the contents of an entire sheet in Excel

7) Excel 2010 vba replacement of application filesearch in Excel

8) How to copy files in Excel VBA is explained here

9) Import txt file in Excel

10) How to do workbook protection with VBA in Excel

 

Here the previous and next chapter