Copying Filtered Data using Excel VBA

Example 1: (Applying single filter)

wb.Activate

With ActiveSheet
.Range(“W1:Y” & lastrow_b).AutoFilter Field:=3, Criteria1:=”B”
End With

Set source_rng = ActiveSheet.Range(“W1:W” & lastrow_W_wb_1).Offset(1, 0)
source_rng.SpecialCells(xlCellTypeVisible).Copy

wbe_out.Activate

Range(“A2”).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Application.CutCopyMode = False

Example 2: (Multiple value filters)

With wk_asm_performance_report_Sales_reporting

.Range(“A2:Y” & LastRow_asm_performance_report_Sales_reporting).AutoFilter Field:=9, Criteria1:=Array(“11”, “13”, “14”, “15”, “50”), Operator:=xlFilterValues

End With

Sheets(“11,13,14,15,50”).Select

With Sheets(“11,13,14,15,50”)
lastrow_check = .Cells(.Rows.Count, “A”).End(xlUp).Row
End With

Range(“A5:N” & lastrow_check).ClearContents

Set source_rng = wk_asm_performance_report_Sales_reporting.Range(“I2:V” & LastRow_asm_performance_report_Sales_reporting).Offset(1, 0)

wk_asm_performance_report_Sales_reporting.Select

source_rng.SpecialCells(xlCellTypeVisible).Copy

Sheets(“11,13,14,15,50”).Select

Range(“A5”).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

With Sheets(“11,13,14,15,50”)
lastrow_check = .Cells(.Rows.Count, “A”).End(xlUp).Row
End With

wk_asm_performance_report_Sales_reporting.ShowAllData

Example 3: (Filtering Non Blank rows)

Sheets(“Sheet1”).Select

With Sheets(“Sheet1”)
lastrow1 = .Cells(.Rows.Count, “A”).End(xlUp).Row
End With

With Sheets(“Sheet1”)
ActiveSheet.Range(“$A$1:$F$” & lastrow1).AutoFilter Field:=5, Criteria1:=”<>”
End With

Set source_rng = Sheets(“Sheet1”).Range(“D1:D” & lastrow1).Offset(1, 0) 

source_rng.SpecialCells(xlCellTypeVisible).Copy

Sheets(“Sheet2”).Select

Range(“A2”).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

 

Clearing the filter of the table

A1: Cell in the table

If Sheets(sht_name).AutoFilterMode = True Then Sheets(sht_name).AutoFilterMode = False

Range(“A1”).Select

If Selection.AutoFilter Then

Range(“A1”).Select
Selection.AutoFilter

End If

About the author: admin

You must be logged in to post a comment.