how to copy data row form one sheet to another with condition

[redsh0es]

Please help me how to copy one data row from one sheet to another. Here's the screenshot:

Now, I want to copy the data row from "DEFECTIVES" sheet to the corresponding sheets according to "Defective unit" (Column C)

If the Defective unit is Television, then all Television rows will go to Television sheet and so on..

I dont know how to start, im not sure if i will include buttons or what.

im not familiar in excel especially in macro codes... Please Please help me..

 

[redsh0es]

someone help me..

 

[teachmehow2macro]

Hi,

To run this macro, hit ALT + F11 when in excel. Insert new module. Add the code in there and run.
This is the best I could do. It will do the job

Sub MoveDefective()

Dim couNter As Long
Dim RowCount As Long

Application.ScreenUpdating = False
RowCount = Range("C65536").End(xlUp).Row
couNter = 1
Do Until couNter > RowCount
If Range("C" & couNter).Value = "Television" Then
Range("C" & couNter).EntireRow.Cut Destination:=Sheets("Television"). _
Range("C65536").End(xlUp).Offset(1, -2)
Range("C" & couNter).EntireRow.Delete
RowCount = RowCount - 1
couNter = couNter - 1
End If
couNter = couNter + 1
Loop


Dim COunterA As Long
Dim RowCountA As Long


RowCountA = Range("C65536").End(xlUp).Row
COunterA = 1
Do Until COunterA > RowCountA
If Range("C" & COunterA).Value = "DVD Player" Then
Range("C" & COunterA).EntireRow.Cut Destination:=Sheets("DVD Player"). _
Range("C65536").End(xlUp).Offset(1, -2)
Range("C" & COunterA).EntireRow.Delete
RowCountA = RowCountA - 1
COunterA = COunterA - 1
End If
COunterA = COunterA + 1
Loop

Dim COunterB As Long
Dim RowCountB As Long

RowCountB = Range("C65536").End(xlUp).Row
COunterB = 1
Do Until COunterB > RowCountB
If Range("C" & COunterB).Value = "Refrigerator" Then
Range("C" & COunterB).EntireRow.Cut Destination:=Sheets("Refrigerator"). _
Range("C65536").End(xlUp).Offset(1, -2)
Range("C" & COunterB).EntireRow.Delete
RowCountB = RowCountB - 1
COunterB = COunterB - 1
End If
COunterB = COunterB + 1
Loop


Application.ScreenUpdating = True

End Sub

I am pretty sure the more experienced people will be able to write something more compact.

 

[redsh0es]

many thanks! your code helped me a lot

it seems that your code moves the content into the corresponding sheets, my goal is actually just copy the data into the corresponding sheets, how can i do that?

 

[teachmehow2macro]

Sub CopyDefective()
 
Dim couNter As Long
Dim RowCount As Long
 
Application.ScreenUpdating = False
RowCount = Range("C65536").End(xlUp).Row
couNter = 1
Do Until couNter > RowCount
If Range("C" & couNter).Value = "Television" Then
Range("C" & couNter).EntireRow.Copy Destination:=Sheets("Television"). _
Range("C65536").End(xlUp).Offset(1, -2)
RowCount = RowCount - 1
couNter = couNter - 1
End If
couNter = couNter + 1
Loop
 
 
Dim COunterA As Long
Dim RowCountA As Long
 
 
RowCountA = Range("C65536").End(xlUp).Row
COunterA = 1
Do Until COunterA > RowCountA
If Range("C" & COunterA).Value = "DVD Player" Then
Range("C" & COunterA).EntireRow.Copy Destination:=Sheets("DVD Player"). _
Range("C65536").End(xlUp).Offset(1, -2)
 
RowCountA = RowCountA - 1
COunterA = COunterA - 1
End If
COunterA = COunterA + 1
Loop
 
Dim COunterB As Long
Dim RowCountB As Long
 
RowCountB = Range("C65536").End(xlUp).Row
COunterB = 1
Do Until COunterB > RowCountB
If Range("C" & COunterB).Value = "Refrigerator" Then
Range("C" & COunterB).EntireRow.Copy Destination:=Sheets("Refrigerator"). _
Range("C65536").End(xlUp).Offset(1, -2)
RowCountB = RowCountB - 1
COunterB = COunterB - 1
End If
COunterB = COunterB + 1
Loop
 
 
Application.ScreenUpdating = True
 
End Sub

Hi not tested but it should do the job

 

[redsh0es]

it didnt work maybe its because of the "loop" word, im not sure..

i prefer your first code, it can handle my problem i'll just do some work around.

how about this one (below) what set of codes will I use? I tried to analyze your first code and create out of that for this new excel but it didint work. i am frustrated programmer

need to move all VENDOR_ASC details into corresponding worksheet.. if its acer then the entire row will go to acer.. how can i do that??