How to Copy a row from sheet1 to sheet2 with "if statement"

[francescoorsini]

This below is the Sheet1

Model	Brand	Status	Quantity	Minimal Quantity
a1	Asus	In stock	2	1
a2	Sony	In stock	1	1
a3	Toshiba	Test	3	1
a4	Fujitsu	Test	1	1

<tbody>
</tbody>

I want to separate in different Sheet according to status.If status=In stock then copy the row and paste it to Sheet2.If status=Test copy the row and paste it to Sheet3.

This below will be Sheet2

Model	Brand	Status	Quantity	Minimal Quantity
a1	Asus	In stock	2	1
a2	Sony	In stock	1	1

<tbody>
</tbody>

This below will be sheet3

Model	Brand	Status	Quantity	Minimal Quantity
a3	Toshiba	Test	3	1
a4	Fujitsu	Test	1	1

<tbody>
</tbody>

 

[JLGWhiz]

Sub copyStuff()
Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet
Set sh1 = Sheets(1) 'Edit sheet name
Set sh2 = Sheets(2) 'Edit sheet name
Set sh3 = Sheets(3) 'Edit sheet name
sh1.UsedRange.AutoFilter 3, "In stock"
sh1.UsedRange.Offset(1).SpecialCells(xlCellTypeVisible).Copy sh2.Cells(Rows.Count, 1).End(xlUp)(2)
sh1.AutoFilterMode = False
sh1.UsedRange.AutoFilter 3, "Test"
sh1.UsedRange.Offset(1).SpecialCells(xlCellTypeVisible).Copy sh3.Cells(Rows.Count, 1).End(xlUp)(2)
sh1.AutoFilterMode = False
End Sub

 

[francescoorsini]

Can we do this without Code? Just with if statement?

 

[JLGWhiz]

Can we do this without Code? Just with if statement?

I can't, but someone else might be able to. If nobody else jumps in today, just click 'Reply' and type 'Bump' in the Quick Reply box and post it. If no reply in two or three days. Start a new thread.

 

[francescoorsini]

ok,thanks for your help.I appreciate it

 

[francescoorsini]

Bump