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.
To do it in Excel, here is the answer:
- Option Explicit
- Sub CopyFilteredData()
- Dim sName As String
- ''
- ''
- sName = "Barbara"
- ''
- 'Filter rows based on Name which is Field 2 (Col AQ).
- ActiveSheet.Range("AP4:AR4").AutoFilter
- ActiveSheet.Range("AP4:AR14").AutoFilter Field:=2, Criteria1:=sName
- ''
- 'Copy filtered table and paste it in Destination cell.
- ActiveSheet.Range("AP4:AR14").SpecialCells(xlCellTypeVisible).Copy
- ActiveSheet.Range("AT4").PasteSpecial Paste:=xlPasteAll
- Application.CutCopyMode = False
- ''
- 'Remove filter that was applied.
- ActiveSheet.AutoFilterMode = False
- ''
- End Sub
Description:
a) Line 13 - after applying filters, use of Specialcells(xlCellTypeVisible) copies only visible (filtered) cells.
Result after Macro execution: