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.
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:AR14").AutoFilter Field:=2, Criteria1:=sName
- 'Copy filtered table and paste it in Destination cell.
- ActiveSheet.Range("AT4").PasteSpecial Paste:=xlPasteAll
- Application.CutCopyMode = False
- 'Remove filter that was applied.
- ActiveSheet.AutoFilterMode = False
- End Sub
a) Line 13 - after applying filters, use of Specialcells(xlCellTypeVisible) copies only visible (filtered) cells.
Result after Macro execution: