Copy entire rows from one workbook to another based on criteria in several columns

  • Hi,


    I have the following code which for some reason doesn't work for me. What I'm trying to do is to take the "Sales1" workbook and extract all rows from Sheet "A" that meet conditions in 2 different columns (for now) and then paste the entire rows into the "CLR_check" workbook, sheet "All".


    After that, I'd like to open another workbook (let's call it "Sales2") which has the same structure as the "Sales1" and again, based on multiple conditions, paste the rows that meet the critera below the range which I've just copied from "Sales1".


    Thank you for any help!

  • Yes, this code is what I have so far (new to Macro).


    In my first version of the code, I only used one condition, "Y" in Sales column, and it somewhat worked - the data in that column got filtered and pasted into the CLR_check file. However, it also copied there the empty cells that didn't contain "Y" in the Sales column.


    When I added the second condition, i.e. "1" in the Product column, the macro stopped working.


    I would love to choose the rows based on 3 conditions and then copy the entire rows (and not copy column by column), if possible.


    By processing the 2 files, do you mean declaring whether each column is Integer/Long etc.?


    Thank you.

  • Before going any further ... when I asked about processing 2 files, I did so because you said:

    "After that, I'd like to open another workbook (let's call it "Sales2") which has the same structure as the "Sales1" "

    ... so, you've mentioned processing 2 files. There is nothing about opening 2 files in your code.

  • OK, well, I'm guess the test of product should be numeric, so I changed that .. the reason you are getting gaps is that you are putting the results into the same row number that you are copying from, so I used a different counter, and I copied 99 columns instead of 2, as you said you wanted more data:

    Sub copy_Sales()

    Dim r As Long

    Dim i As Long

    Dim Sales As Workbook

    Set Sales = Workbooks.Open("C:\Users\Sales.xls")

    Dim CLR_check As Workbook

    Set CLR_check = Workbooks.Open("C:\Users\CLR_check.xltm")

    CLR_check.Sheets("All").Activate

    Rows("2:65536").ClearContents

    Sales.Sheets("A").Activate

    Sales_column = 2

    Product_column = 5

    r = 2

    For i = 2 To 10000

    If IsEmpty(Sheets("A").Cells(i, 1)) Then Exit For

    If Cells(i, Sales_column).Value = "Y" And _

    Cells(i, Product_column).Value = 1 Then

    CLR_check.Worksheets("All").Cells(r, 1).Resize(1, 99).Value = Sales.Sheets("A").Cells(i, 1).Resize(1, 99).Value

    r = r + 1

    End If

    Next i

    End Sub

  • Hi Glenn,


    I've tried to run the code and seems like there is still some issue with it.


    Right now, I'm only testing the code on a data file (Sales) with 6 data points - so I can see, that based on the 2 conditions we applied, 3 data points should have been filtered out and copied to the CLR_check file. However, only two were copied, and it seems it could be because the code checks every second row starting from row 2. I've also increased the data points to 10, and I'm pretty sure that's what the code is doing based on the results. But I don't see anything that would indicate such thing in the code itself.


    And out of curiosity, why did you change the initial variable setting (r & i) to Long from Integer? :)


    Thanks a lot!

  • No, it doesn't check every second row ... what makes you think that?


    Attach your files, and I'll take a look at the reason why it isn't working.


    I changed the variables from Integer to Long in case you change the range to be beyond 32,767 ... that's the size limit of Integer variables.

  • I think there was some problem with the original data file because when I created a new one, it started to work perfectly.


    I've also started to work on the second part - copying the data from the second file below the data we've just copied (the row can be different each time based on how many rows get copied from the original Sales file). Seems like it would still need some revision..


    Thank you for your help!

  • Your line of code:
    ThisWorkbook.Worksheets("All").Cells(Rows.Count, 1).End(xlUp).Resize(1, 99).Value = Sales2.Sheets("B").Cells(i, 1).Resize(1, 99).Value

    overwrites the last row again and again.


    Use this:

    ThisWorkbook.Worksheets("All").Cells(Rows.Count, 1).End(xlUp).Offset(1,0).Resize(1, 99).Value = Sales2.Sheets("B").Cells(i, 1).Resize(1, 99).Value