Solved: Loop problem

[gussi]

Hi everyone,
I?m having a problem with making a loop in my code.
I?m taking in Data to Sheet3 and it depends on which data i?m taking in how long it is. But it is always in Column from "A:N".
When Data is in Sheet3 I sort it by Column D and there is only two options, either K or S.
Now I think I need to make a Loop to take all Data with K in D Column (I need the whole row, that is from A:N) to Sheet4 and all Data with S in D Column to Sheet5.
What should i do?

Regards, Gussi.

[Bob Phillips]

[vba]

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim iLastRow As Long
Dim cell As Range
Dim sh As Worksheet

With Worksheets("Sheet3")

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
.Range("A1").Resize(iLastRow, 14).Sort key1:=.Range("K1"), header:=xlYes
On Error Resume Next
Set cell = .Range("K2").Resize(iLastRow - 1).Find("S")
On Error GoTo 0
If cell Is Nothing Then
.Range("A1").Resize(iLastRow, 14).Copy Worksheets("Sheet4").Range("A1")
Else
.Range("A1").Resize(cell.Row - 1, 14).Copy Worksheets("Sheet4").Range("A1")
.Range("A" & cell.Row).Resize(iLastRow - cell.Row + 1, 14).Copy Worksheets("Sheet5").Range("A2")
.Rows(1).Copy Worksheets("Sheet5").Range("A1")
End If

End With

End Sub
[/vba]

[gussi]

Thanks for that..

But I don?t know why you put "K1" in this..
I?m working in column D in Sheet3 and there are only K and S
I need all data from A:N with K in D column to go to Sheet4 and the same thing if there is S in column D i need that data from A:N in Sheet5...

[Bob Phillips]

Mixed my columns up, was thinking K when it should have been D.

[gussi]

Thanks man.. It works..
I?m new to VBA so thank you very much.
There?s only one problem..
There is one line with K on top in Sheet5 where Rows with S go.
I have no header in these sheets if that?s the problem.

[gussi]

The Top line in Sheet4 is also in Sheet5, everything else works properly?

[Bob Phillips]

[vba]

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim iLastRow As Long
Dim cell As Range
Dim sh As Worksheet

With Worksheets("Sheet3")

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
.Range("A1").Resize(iLastRow, 14).Sort key1:=.Range("D1"), header:=xlYes
On Error Resume Next
Set cell = .Range("D1").Resize(iLastRow - 1).Find("S")
On Error Goto 0
If cell Is Nothing Then
.Range("A1").Resize(iLastRow, 14).Copy Worksheets("Sheet4").Range("A1")
Else
.Range("A1").Resize(cell.Row - 1, 14).Copy Worksheets("Sheet4").Range("A1")
.Range("A" & cell.Row).Resize(iLastRow - cell.Row + 1, 14).Copy Worksheets("Sheet5").Range("A1")
End If

End With

End Sub
[/vba]

[gussi]

Thanks xld..

Regards, Gussi