How do I copy rows to second worksheet if Cell value meets criteria based on a moving range


Hi, I am trying to copy data from one worksheet to another based on the value in the variance column. If the variance is >= 10 or <= -10 then copy the rows data into the investigations sheet.

The All differences sheet is updated daily with a number of new lines based on the date and the number of tills used. I dont know how to make the data range change with every day that i add new data. Any assistance would be greatly appreciated.




There's probably an annoying array formula that will do it, but I'd just use this macro instead:

Sub copy_data()

Set cell_range = Sheets("All Differences").Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)

For Each cell In cell_range

    If Abs(Sheets("All Differences").Cells(cell.Row, 16).Value) >= 10 Then

        Sheets("All Differences").Range("A" & cell.Row).EntireRow.Copy _
        Sheets("Investigation").Cells(Sheets("Investigation").Range("A" & Rows.Count).End(xlUp).Row + 1, 1)

    End If

Next cell

End Sub

I tested it on sample data and then changed the sheet references so it should work with your data. Try it on a copy of the data first.


Answer the Question

You must create an account to use the forum. Create an Account or Login