In visual basic

How do i find a row in sheet 1 which has "TEST" in colum K and then in sheet
2 find the next blank row and paste the hole row from sheet 1 into sheet 2.
there could be 100 rows that have "test" in.

Many thanks

Perhaps use datafilterautofilterfilter the column with test by
testcopypaste to the other sheet row. Record a macro while doing and then
modify to find the last row by using something like.

lastrow=sheets("sheet2").cells(rows.count,"a").end (xlup).row+1
range("yourfilteredrange").copy sheets("sheet2").cells(lastrow,"a")

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Chuck" wrote in message
...
In visual basic

How do i find a row in sheet 1 which has "TEST" in colum K and then in
sheet
2 find the next blank row and paste the hole row from sheet 1 into sheet
2.
there could be 100 rows that have "test" in.

Many thanks

Maybe this in a module

Sub copyit()
Dim MyRange, MyRange1 As Range
Sheets("Sheet1").Select
LastRow = Sheets("Sheet1").Range("K65536").End(xlUp).Row
Set MyRange = Sheets("Sheet1").Range("K1:K" & LastRow)
For Each c In MyRange
If c.Value = "Test" Then
If MyRange1 Is Nothing Then
Set MyRange1 = c.EntireRow
Else
Set MyRange1 = Union(MyRange1, c.EntireRow)
End If
End If
Next
MyRange1.Select
Selection.Copy
Sheets("Sheet2").Select
Range("A1").Select
ActiveSheet.Paste
End Sub


Mike

"Chuck" wrote:

In visual basic

How do i find a row in sheet 1 which has "TEST" in colum K and then in sheet
2 find the next blank row and paste the hole row from sheet 1 into sheet 2.
there could be 100 rows that have "test" in.

Many thanks

Many thanks for the help...... i keep getting errors!!!!!!!
I get: "Object variable or With block variable not set" or "Error 91", i
then de-bug, MyRange1.Select
i also can't find out how to loop this around to keep searching paste into
the first free space on sheet 2

Sorry new to all this......


"Mike H" wrote:

Maybe this in a module

Sub copyit()
Dim MyRange, MyRange1 As Range
Sheets("Sheet1").Select
LastRow = Sheets("Sheet1").Range("K65536").End(xlUp).Row
Set MyRange = Sheets("Sheet1").Range("K1:K" & LastRow)
For Each c In MyRange
If c.Value = "Test" Then
If MyRange1 Is Nothing Then
Set MyRange1 = c.EntireRow
Else
Set MyRange1 = Union(MyRange1, c.EntireRow)
End If
End If
Next
MyRange1.Select
Selection.Copy
Sheets("Sheet2").Select
Range("A1").Select
ActiveSheet.Paste
End Sub


Mike

"Chuck" wrote:

In visual basic

How do i find a row in sheet 1 which has "TEST" in colum K and then in sheet
2 find the next blank row and paste the hole row from sheet 1 into sheet 2.
there could be 100 rows that have "test" in.

Many thanks