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

[francescoorsini]

This below is the Sheet1

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Model[/TD]
[TD]Brand[/TD]
[TD]Status[/TD]
[TD]Quantity[/TD]
[TD]Minimal Quantity[/TD]
[/TR]
[TR]
[TD]a1[/TD]
[TD]Asus[/TD]
[TD]In stock[/TD]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]a2[/TD]
[TD]Sony[/TD]
[TD]In stock[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]a3[/TD]
[TD]Toshiba[/TD]
[TD]Test[/TD]
[TD]3[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]a4[/TD]
[TD]Fujitsu[/TD]
[TD]Test[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

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
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Model[/TD]
[TD]Brand[/TD]
[TD]Status[/TD]
[TD]Quantity[/TD]
[TD]Minimal Quantity[/TD]
[/TR]
[TR]
[TD]a1[/TD]
[TD]Asus[/TD]
[TD]In stock[/TD]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]a2[/TD]
[TD]Sony[/TD]
[TD]In stock[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


This below will be sheet3

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Model[/TD]
[TD]Brand[/TD]
[TD]Status[/TD]
[TD]Quantity[/TD]
[TD]Minimal Quantity[/TD]
[/TR]
[TR]
[TD]a3[/TD]
[TD]Toshiba[/TD]
[TD]Test[/TD]
[TD]3[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]a4[/TD]
[TD]Fujitsu[/TD]
[TD]Test[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

 

[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