visual basic. find copy row past into new sheet

[Chuck]

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

 

[Don Guillett]

Perhaps use data>filter>autofilter>filter the column with test by
test>copy>paste 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")

 

[Mike H]

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]

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......