Hi try adapat code below but, display error.
i need filetr by date and copy, but my criteria is another sheet "Corr_KPI" then range("O1") has formulas, when i run code, dysplay error
compilation error:
the object is mandatoryFilterDate.xlsb![]()
Sub Macro9() 'http://www.mrexcel.com/forum/excel-questions/818192-macro-filter-copy-specific-columns-another-workbook.html Dim wsSource As Worksheet Dim wsTarget As Worksheet Dim MyDate As Date 'worksheets and workbooks Set wsSource = Worksheets("BD") Set wsTarget = Worksheets("Corr_KPI") Set MyDate = wsTarget.Range("O1").Value 'clear data wsTarget.Range("A2").CurrentRegion.Offset(1).ClearContents 'filter and copy With wsSource.Range("A2:O" & wsSource.Range("A" & Rows.Count).End(xlUp).row) ''''''''''''''''''''''''''''''''''''' dd/MM/yyyy .AutoFilter Field:=11, Criteria1:=MyDate .Offset(1).Columns(3).Resize(, 2).SpecialCells(xlVisible).Copy wsTarget.Range("A2") .Offset(1).Columns(6).SpecialCells(xlVisible).Copy wsTarget.Range("C2") .Offset(1).Columns(10).Resize(, 2).SpecialCells(xlVisible).Copy wsTarget.Range("D2") .Offset(1).Columns(13).SpecialCells(xlVisible).Copy wsTarget.Range("F2") End With 'turn to values in the target sheet With wsTarget.Range("A2").CurrentRegion .Value = .Value End With Application.Goto wsTarget.Range("A2") Set wsSource = Nothing Set wsTarget = Nothing End Sub
Bookmarks