How to Transfer a row to another sheet based on a value in a cell

[Flavien]

Hello Experts!

How would you remove blank lines (cf. picture)? Please Help!

Public Sub CopyRows()
Sheets("EXTRACTION").Select
' Find the last row of data
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
' Loop through each row
For x = 2 To FinalRow
' Decide if to copy based on column G (A=1;E=5...)
ThisValue = Cells(x, 7).Value
If ThisValue = "Completed" Then
Cells(x, 1).Resize(1, 33).Cut
Sheets("Completed").Select
NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
Cells(NextRow, 1).Select
ActiveSheet.Paste
Sheets("EXTRACTION").Select
ElseIf ThisValue = "Rejected" Then
Cells(x, 1).Resize(1, 33).Cut
Sheets("Rejected").Select
NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
Cells(NextRow, 1).Select
ActiveSheet.Paste
Sheets("EXTRACTION").Select
End If
Next x
End Sub

 

[DanteAmor]

Try this:

Sub CopyRows()
  Dim sh1 As Worksheet
  Dim arr As Variant, itm As Variant
  Dim lr As Long
  Application.ScreenUpdating = False
 
  Set sh1 = Sheets("EXTRACTION")
  arr = Array("Completed", "Rejected")
 
  On Error Resume Next: If sh1.FilterMode Then sh1.ShowAllData: On Error GoTo 0
  For Each itm In arr
    lr = sh1.Range("G" & Rows.Count).End(3).Row
    sh1.Range("A1:G" & lr).AutoFilter 7, itm
    With sh1.Range("A2").Resize(lr, 31)
      .Copy Sheets(itm).Range("A" & Rows.Count).End(3)(2)
      .EntireRow.Delete
    End With
    sh1.ShowAllData
  Next

  Application.ScreenUpdating = True
End Sub

Please consider the following.
Note Code Tag:
In future please use code tags when posting code.
How to Post Your VBA Code it makes your code easier to read and copy and it also maintains VBA formatting.
---------------
NOTE XL2BB:
For the future, it would help greatly if you could give us the sample data in a form that we can copy to test with, rather that a picture.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in
Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
--------------
Example:

Dante Amor
	A	B	C	D	E	F	G
1	No Plan	Indice	Designation	Client	Createur	Date	Progress Status
2	P01	A	b	cl1	fla	08-ene	Pending
3	P02	B	c	cl2	fla	09-ene	Collected
4	P03	C	d	cl3	fla	10-ene	Pending
5	P04	D	e	cl4	fla	11-ene	Collected
6	P05	E	f	cl5	fla	12-ene	Completed
7	P06	F	g	cl6	fla	13-ene	Rejected
8	P07	G	h	cl7	fla	14-ene	Completed
9	P08	H	i	cl8	fla	15-ene	Rejected
10	P09	I	j	cl9	fla	16-ene	Pending
11	P10	J	k	cl10	fla	17-ene	Collected
12	P11	K	l	cl11	fla	18-ene	Pending
13	P12	L	m	cl12	fla	19-ene	Collected
14	P13	L	m	cl13	fla	20-ene	Completed
EXTRACTION

 

[Flavien]

Hello DanteAmor,

Thank you for your help!
I tried your macro but unfortunately either the rows are hidden or deleted even with "key words" in column G.

Finally this test allowed me to review my need. I would like to send only certain cells of the row in specific columns of my second table (Suivi) and then delete the copied rows.

Could you please help?

Please find enclosed an example. Sorry I couldn't use the macro “XL2BB”; I'll try again later! because I don't understand how it works.

Thanks a lot for your help!