vba copy filtered data in Excel

Q172. In Excel, how can I filter and copy only filtered data using VBA?

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 excel vba copy filtered data

 

You can find similar Excel Questions and Answer hereunder

1) How can I list all files in a folder using VBA?

2) I have a macro that takes a lot of time for execution - how can I keep the user informed that the macro is running?

3) How can I clear cell after activating a routine when there is a change in value of a cell?

4) How can I dynamically add series to an existing chart using VBA?

5) How do I enter a formula in a cell using VBA (using Absolute Reference)?

6) How can I protect / unprotect WorkSheet using VBA?

7) String split in vba in Excel

8) How do I add a symbol like Triangle / Inverted Triangle for indicating trends in a cell using VBA?

9) How can I loop through all ActiveX checkboxes in WorkSheet and set them to Unchecked status?

10) How can I set the fill color, font color and set number format of cell to date?

11) How can I get users to select a file for processing using my macro?

12) I have data arranged in rows of information. Is there any way I can enter new data through a form instead of entering it directly in SpreadSheet?

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

14) How can I filter a table to get all records that have less than a particular value in a specific column?

15) How can worksheet functions be accessed in VBA?