Filter and copy data from one sheet to another

Question

I have a problem filtering the date from the first sheet of a workbook and then copying everything in another workbook. In the first part of my code, I'm selecting the workbook I want to work with. When I'm running my code it just copies all the data, without filtering it and i don't know what it's wrong about it.

Sub macro()

Dim customerBook As Workbook
Dim filter As String
Dim caption As String
Dim customerFilename As String
Dim customerWorkbook As Workbook
Dim targetWorkbook As Workbook
Dim LastRow As Long

' make weak assumption that active workbook is the target
Set targetWorkbook = Application.ActiveWorkbook

' get the customer workbook
filter = "Text files (*.xlsx),*.xlsx"
caption = "Please select an input file "
customerFilename = Application.GetOpenFilename(filter, , caption)

Set customerWorkbook = Application.Workbooks.Open(customerFilename)

    Sheets("Sheet1").Activate

    LastRow = Range("A1").CurrentRegion.Rows.Count
    Range("A1:CA" & LastRow).Select

    Selection.AutoFilter Field:=39, Criteria1:=Array( _
        "0", "1", "2", "3"), Operator:=xlFilterValues

    Selection.AutoFilter Field:=12, Criteria1:=">=1/1/2018", _
                        Operator:=xlFilterValues, _
                        Criteria2:="<=12/31/2019"
    Selection.Copy
    Cells.Select

    Dim dest As Range
    With Workbooks("data.xlsm").Worksheets(3)

    Sheets("data").Range("A:CA").ClearContents

    Set dest = .Range("A1")
    Selection.Copy dest
    End With
    Selection.AutoFilter
' Close customer workbook
customerWorkbook.Close False

End Sub

Please read this https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba — Tim Stack, Apr 10 '19 at 11:50
Possible duplicate of [Copy filtered data to another sheet using VBA](https://stackoverflow.com/questions/39121960/copy-filtered-data-to-another-sheet-using-vba) — Tim Stack, Apr 10 '19 at 11:53
You are doing a lot of `Select` (which @TimStack reply should cover) but no where are you specifying which workbook you want to work with (i.e. `Sheets("Sheet1").Activate`.. this could be your `customerWorkbook` workbook or the one you are opening). Try to be specific on which workbook you want to refer to. Also, have a look at `With` statement — Zac, Apr 10 '19 at 11:56
@MaziluIsabelaElena you don't understand, that is only the sheet you are specifying. A sheet with name "Sheet1" could be present in any workbook. You will have to specify the WB as well, e.g. by `Workbooks(customerFilename).Sheets("Sheet1")` — Tim Stack, Apr 10 '19 at 12:12

score 0 · Accepted Answer · answered Apr 10 '19 at 12:21

0

You Just need to add Selection.SpecialCells(xlCellTypeVisible).Copy instead of Selection.Copy in your code.

answered Apr 10 '19 at 12:21

Mohit Chauhan

78
2
7

Filter and copy data from one sheet to another

1 Answers1