visual basic. find copy row past into new sheet
 

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

visual basic. find copy row past into new sheet
 

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

visual basic. find copy row past into new sheet
 

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

visual basic. find copy row past into new sheet
 

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