Search then copy and past data

[joecrabtree]

To all,

I have taken this code from the MS VBA Help File. How can I modify it
so that instead of changing the pattern of the cell, it instead copys
the entire row that the cell is in, and pastes it into another workbook
called test1.xls, sheet1, cell A2?

Thanks in advance,

Joseph Crabtree


With Worksheets(1).Range("a1:a500")
Set c = .Find(2, lookin:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Interior.Pattern = xlPatternGray50
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With

 

[Don Guillett]

try this

Do
c.entirerow.copy workbooks("test1.xls").sheets("sheet1").range("A2")

 

[Tom Ogilvy]

The code you show is for finding multiple instances. If that is the case,
then I assume you wouldn't want subsequent found instances to be copied over
ones already copied

With Worksheets(1).Range("a1:a500")
Set c = .Find(2, lookin:=xlValues)
i = 0
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.entireRow.copy Worksheets("Test1.xls").Worksheets( _
"Sheet1").Range("A1")(i)
i = i + 1
Set c = .FindNext(c)
Loop While c.Address <> firstAddress
End If
End With

 

[Tom Ogilvy]

A couple of typos: It should be:

With Worksheets(1).Range("a1:a500")
Set c = .Find(2, lookin:=xlValues)
i = 1
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.entireRow.copy Worksheets("Test1.xls").Worksheets( _
"Sheet1").Range("A2")(i)
i = i + 1
Set c = .FindNext(c)
Loop While c.Address <> firstAddress
End If
End With