200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
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.