Copy rows from one worksheet to another based on criteria

[twildone]

I would like to copy entire rows from a master worksheet to destination worksheets based on the result in column C for the row in the master worksheet. Below is the code and everytime I run it, it copies the rows to the correct destination worksheet but it copies to the next available and open row that does not have any data in it. I would like for it to always copy the rows to the destination worksheet to begin at Row 2 regardless if there is data or not. I would greatly appreciate any suggestions.....thanks


<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
Public Sub CopyRows()
Sheets("Data").Select
' Find the last row of data
FinalRow = Range("A65536").End(xlUp).Row
' Loop through each row
For x = 2 To FinalRow
' Decide if to copy based on column C
ThisValue = Range("C" & x).Value
If ThisValue = "LIR" Then
Range("A" & x & ":AG" & x).Copy
Sheets("LIR").Select
NextRow = Range("A65536").End(xlUp).Row + 1
Range("A" & NextRow).Select
ActiveSheet.Paste
Sheets("Data").Select
ElseIf ThisValue = "KLE" Then
Range("A" & x & ":AG" & x).Copy
Sheets("KLE").Select
NextRow = Range("A65536").End(xlUp).Row + 1
Range("A" & NextRow).Select
ActiveSheet.Paste
Sheets("Data").Select
End If
Next x
<o> </o>
End Sub

 

[gssachin]

Thanks a lot sir, It work as I required.

 

[Peter_SSs]

Thanks a lot sir, It work as I required.

Glad to help.