copy rows based on value

[already]

Hi

I'm using following code to copy rows to another sheet based on the value in column F. I can't combine the code to copy the different values to different sheets (ex all rows with value 'test1' go to sheet2 all with 'test2" go to sheet 3)without having empty rows. When I run different macros to do this I don't have any problem. The goal is to sort 1 sheet to 3 sheets (3 different values in column F)
Any help is welcome.

Thanks

Al
part of the code

If Range("F" & CStr(LSearchRow)).Value = "test" Then<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select<o></o>
Selection.Copy<o></o>
<o></o>
Sheets("Sheet2").Select<o></o>
Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select<o></o>
ActiveSheet.Paste<o></o>
<o></o>
'Move counter to next row<o></o>
LCopyToRow = LCopyToRow + 1<o></o>
<o></o>
Sheets("Sheet1").Select<o></o>
<o></o>
End If<o></o>

 

[Sthrncali]

This code worked for me:

Sub MoveRows()
Dim i As Integer
Dim Str1 As String, Str2 As String, Str3 As String
Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
Str1 = "First Search Term"  'Add your search terms
Str2 = "Second Search Term" 'Add your search terms
Str3 = "Third Search Term" 'Add your search terms
 
'Set Destination Worksheets
Set ws1 = Worksheets("Sheet2") 
Set ws2 = Worksheets("Sheet3")
Set ws3 = Worksheets("Sheet4")
 
For r = 1 To ActiveSheet.Range("F65536").End(xlUp).Row
    If Cells(r, 6).Value = Str1 Then
    i = ws1.Range("A65536").End(xlUp).Offset(1, 0).Row
    ws1.Rows(i).Value = ActiveSheet.Rows(r).Value
    End If
    If Cells(r, 6).Value = Str2 Then
    i = ws2.Range("A65536").End(xlUp).Offset(1, 0).Row
    ws2.Rows(i).Value = ActiveSheet.Rows(r).Value
    End If
    If Cells(r, 6).Value = Str3 Then
    i = ws3.Range("A65536").End(xlUp).Offset(1, 0).Row
    ws3.Rows(i).Value = ActiveSheet.Rows(r).Value
    End If
Next
End Sub

 

[already]

Thanks very much for your help. It is wortking 100%

Kind regards

Al

 

[ihatetivo]

Wow. This works for me in Excel 2010 as well. I had to search over 20 forums to find a simple, adaptable macro code. Thank you, Sthrncali!

 

[ihatetivo]

How might Sthrncali's code be modified to overwrite (rather than append) the data that already exists in the destination worksheets? If so, could someone kindly paste the final code excerpt?

Getting greedy, but could the code be modified further to always copy the first row of the active sheet, before performing the criteria-based copy/paste that Sthrncali's code already does?

Thank you for the help. I didn't know I needed VBA skills until a couple of days ago. This forum has been a big help to me, allowing me to move forward on my project even though my VBA learning has only started.