June 18, 2020

Copy Paste Filtered Data to Another Workbook

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

Copy Paste Filtered Data to Another Workbook

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


End With

End Sub

Watch the training video below:

Copy Paste Filtered Data to Another Workbook

Further References:

Advanced Filter


Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.