Hello Emily,
Apologies. I should have told you to also remove this line of code besides the other one in post #4:-
Columns("A").SpecialCells(4).EntireRow.Delete
also , sorry to be a pain, but each time i click to re run the data, it pastes it all again, so i am then getting duplicated entries, is there a way in which it will just paste the new ones that havent already been transferred?
This is happening because we are no longer deleting the "used" rows of data because you need the data base to keep all entries. However, not to worry. Just add the following line to the code:-
Sheets("Successful Projects").Range("A2:R" & lRow).ClearContents
just after this line of code:-
lRow = Range("A" & Rows.Count).End(xlUp).Row
There should no longer be any duplicates in the "Successful Projects" sheet. You will need to change the cell references ("A2:R") in the new line of code to suit yourself ("A2:BP" ?).
I'm just referring to the CopyIT2 macro above in Module 2.
Also, just remember that the code will only transfer the rows of data that have "Successful" placed in Column R.
So the whole adjusted code should look like this:-
Sub CopyIt2()
Application.ScreenUpdating = False
Dim lRow As Long
lRow = Range("A" & Rows.Count).End(xlUp).Row
Sheets("Successful Projects").Range("A2:R" & lRow).ClearContents
Sheets("Input").Select
For Each cell In Range("R2:R" & lRow)
If cell = "Successful" Then
Range(Cells(cell.Row, "C"), Cells(cell.Row, "L")).Copy
Sheets("Successful Projects").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
End If
Next
Application.ScreenUpdating = True
Application.CutCopyMode = False
Sheets("Successful Projects").Select
End Sub
Again, change all cell references to suit yourself.
I hope that this helps.
Cheerio,
vcoolio.