Copy rows based on a condition

[Dwin123]

I had created worksheet in Excel file which consists of some data in colums and rows. Now i need to create a code where i can copy entire row into separate worksheet depending on it's data. So i ask my friend about it he told me that this is possible in VBA but don't have much more idea about it. Can anyone help me out with this issue.

[Vibhas]

If you want to create a code where i can copy entire row into separate worksheet depending on it's data then i would suggest you to use the below code and have a check for the same hope it works

Public Sub patxlb()

Dim lng_Dest_Row As Long
Dim str_Dest_Sht As String

For i = 2 To Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Rowd
    str_Dest_Sht = Sheets("Data").Cells(i, "G").Value
    lng_Dest_Row = Sheets(str_Dest_Sht).Cells(Rows.Count, 1).End(xlUp).Row + 1
    Sheets("Data").Rows(i).Copy Destination:=Sheets(Sheets("Data").Cells(i, "G").Value).Cells(lng_Dest_Row, 1)
Next i

End Sub

[Dwin123]

Thanks for replying me i had try to use the above code it's not working for me, whenever i am trying to use the code it's giving me some error code which i had forget, does anyone knows any why i am getting error code or can provide me any other code where which will work properly as per my requirements.

[Aarpitt]

Hey buddy use this code i am sure you won't face any issue because i had already tested this code and it's workign fine at my place, if you get any error message then it seems that you had made some mistake

Public Sub patxlb()

Dim lng_Dest_Row As Long
Dim str_Dest_Sht As String

For i = 2 To Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row
    str_Dest_Sht = Sheets("Data").Cells(i, "G").Value
    lng_Dest_Row = Sheets(str_Dest_Sht).Cells(Rows.Count, 1).End(xlUp).Row + 1
    Sheets("Data").Rows(i).Copy Destination:=Sheets(Sheets("Data").Cells(i, "G").Value).Cells(lng _Dest_Row, 1)
Next i

End Sub

[Harper]

Hello friends,

I am also having similar type of issue where i want to copy entire row of data from my worksheet to another worksheet called as EE where Column P contains Terms. I had tried out many code but none of them are workign properly for me, can anyone help me out with it because it's very important for me. Please help

[Mecurtis]

Try to use the below code where i am assuming that sheet one has a heading on first row and data starts from Row 2 and P column doesn't have any empy cells in it and EE sheet will have heading so that the data will start copying from row 2

Set i1 = Sheets("sheet one ") 
Set e1 = Sheets("EE") 
Dim d1 
Dim j1 
d1 = 1 
j1 = 2 

Do Until IsEmpty(i1.Range("Q" & j1)) 

If i1.Range("Q" & j1) = "TERM" Then 
d1 = d1 + 1 
e1.Rows(d).Value = i1.Rows(j1).Value 

End If 
j1 = j1 + 1 
Loop