i wonder if anybody can help me. i am putting together a sheet that will hold a large amount of raw data. from that data i would like to filter results and copy certain cell references to the next empty row on another sheet.
I have been able to put together some code that will search down column D on my ‘Raw Data’ Sheet and if the word Missed is found it will copy Cells A and D onto another sheet – ‘Instances, starting in cells A2 and B2 and will copy into the next empty row.
What I would like to do is either modify what I have or even re-write and have two separate macros. So that it in addition to that search it also searches column D for the word ‘Busy’ and any instance in Column I where the duration is above 00:04:00 and then copy those results to the next empty row on my ‘Instances Sheet’ I have attached a sample Workbook.
I have also attached my code but I am not sure on how to modify what I have to search for two values in two separate columns
Thank you in advance for any help you can provide.
- Sub Missed()
- Dim Cell As Range
- With Sheets("Raw Data") 'Sheet with data to check for value
- ' loop column D until last cell with value (not entire column)
- For Each Cell In .Range("D1:D" & .Cells(.Rows.Count, "D").End(xlUp).Row)
- If Cell.Value = "Missed" Then
- NextFreeRow = Sheets("Instances").Cells(Sheets("Instances").Rows.Count, "A").End(xlUp).Row + 1
- 'get the next empty row to paste data to
- .Range("A" & Cell.Row & ",D" & Cell.Row).Copy Destination:=Sheets("Instances").Range("A" & NextFreeRow)
- End If
- Next Cell
- End With
- End Sub