Search then copy and past data
 

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

Search then copy and past data
 

try this

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


--
Don Guillett
SalesAid Software

"joecrabtree" wrote in message
s.com...
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

Search then copy and past data
 

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


--
Regards,
Tom Ogilvy



"joecrabtree" wrote in message
s.com...
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

Search then copy and past data
 

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

--
Regards,
Tom Ogilvy



"Tom Ogilvy" wrote in message
...
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


--
Regards,
Tom Ogilvy



"joecrabtree" wrote in message
s.com...
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