Hi,
I'm trying to copy rows based on if a the cell has a specific value.
The code I tried :
The Status however is a formula that return Found or New, so may this be the issue?
The result I'm getting now is a mix of found and new.
Thank you
David
I'm trying to copy rows based on if a the cell has a specific value.
The code I tried :
VBA Code:
Sub Copy()
Dim StatusCol As Range
Dim Status As Range
Dim PasteCell As Range
Set StatusCol = ThisWorkbook.Worksheets("Import").Range("G2:G5000")
For Each Status In StatusCol
If ThisWorkbook.Worksheets("NEW").Range("A2") = "" Then
Set PasteCell = ThisWorkbook.Worksheets("NEW").Range("A2")
Else
Set PasteCell = ThisWorkbook.Worksheets("NEW").Range("A1").End(xlDown).Offset(1, 0)
End If
If Status = "New" Then Status.EntireRow.Copy PasteCell
Next Status
End Sub
Agenda IFS.xlsm | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
1 | Name | date | type | Unannounced | standard | external_certNr | Status | ||
2 | test1 | 07/10/2022 | repeat | Unannounced | IFS L | COID: 79631 | New | ||
3 | test2 | 01/12/2022 | repeat | Unannounced | IFS v7 | New | |||
4 | test3 | 05/10/2022 | repeat | Unannounced | IFS v7 | COID: 60342 | New | ||
5 | test4 | 17/10/2022 | repeat | Announced | IFS v7 | coid 36932 | New | ||
6 | test5 | 05/12/2022 | repeat | Announced | IFS L | COID: 60157 | New | ||
7 | test6 | 28/10/2022 | repeat | Unannounced | IFS v7 | COID: 11763 | New | ||
8 | test7 | 28/11/2022 | repeat | Announced | IFS v7 | 81040 | New | ||
9 | test8 | 16/12/2022 | repeat | Announced | IFS v7 | New | |||
Import |
Cell Formulas | ||
---|---|---|
Range | Formula | |
G2:G9 | G2 | =IFERROR(VLOOKUP([@Test],Archive!K:L,2,FALSE),"New") |
The Status however is a formula that return Found or New, so may this be the issue?
The result I'm getting now is a mix of found and new.
Thank you
David