How to copy paste filtered data to another workbook automatically using VBA.
Below is the complete VBA code with some code-lines commented:
Sub CopyAutoFilteredDataToAnotherWorkbook()
Dim OtherWorkbook As Workbook
Dim myDB As Range With ActiveWorkbook.Sheets(“Sheet1”)
Set myDB = .Range(“A1:B1”).Resize(.Cells(.Rows.Count, 1).End(xlUp).Row) ‘myDB.Select
End With
With myDB .AutoFilter field:=2, Criteria1:=”Recruiting Manager” .SpecialCells(xlCellTypeVisible).Copy
End With
Set OtherWorkbook = Workbooks.Open(“C:\Users\takyar\Desktop\test-for-filtered-data.xlsx”)
OtherWorkbook.Sheets(1).Activate Range(“A1”).PasteSpecial Paste:=xlPasteValuesAndNumberFormats ‘Paste:=xlPasteAll Range(“A1”).CurrentRegion.Columns.AutoFit Range(“C1”).Select ‘wbNewWorkbook.Close savechanges:=True
With myDB
.AutoFilter
End With
End Sub
Watch the training video below:
Further References: