Find and copy in Excel
 

I need a macro that will search for all occurances of the word "tool" in
col B of a spreadsheet and then copy the entire row of each occurance
and paste the rows in another worksheet within the same spreadsheet


--
TonyG001
------------------------------------------------------------------------
TonyG001's Profile: http://www.excelforum.com/member.php...o&userid=35737
View this thread: http://www.excelforum.com/showthread...hreadid=555185

Find and copy in Excel
 

Try this friend....
I know you can use advancedfilter too, but this is a quick and dirty
one...

Sub CopyRowsBasedOnCellContents()
Dim SourceTab As String
Dim DestTab As String
Dim DestRow As Integer
Dim SearchText As String

'TURN SCREEN UPDATED ON TO KEEP YOU
'FROM SWOLLOWING YOUR TOUNG IF YOU
'HAVE A BUNCH OF ROWS.
Application.ScreenUpdating = False


SourceTab = "Sheet1"
DestTab = "Sheet2"
SearchText = "Tool"

For x = 1 To 99 'YOU'LL NEED TO EDIT THIS TO ACCOUNT FOR THE ROWS
YOUR READING

If InStr(UCase(Sheets(SourceTab).Cells(x, "B")),
UCase(SearchText)) < 0 And Trim(Sheets(SourceTab).Cells(x, "B")) < ""
Then
Sheets(SourceTab).Rows(x & ":" & x).Copy
Sheets(DestTab).Select
Sheets(DestTab).Range("B1").Select

If Trim(Sheets(DestTab).Range("B1").Value) < "" Then
DestRow = Sheets(DestTab).Range("B1").End(xlDown).Row +
1
Sheets(DestTab).Rows(DestRow & ":" & DestRow).Select
End If

ActiveSheet.Paste
Sheets(SourceTab).Select

End If

Next x

Application.ScreenUpdating = True
End Sub

Regards,
Jamie





TonyG001 wrote:
I need a macro that will search for all occurances of the word "tool" in
col B of a spreadsheet and then copy the entire row of each occurance
and paste the rows in another worksheet within the same spreadsheet

Find and copy in Excel
 

Or try this

Sub FindTool()
Dim myRange As Range
Set myRange = Range("B:B")
For Each Cell In myRange
If UCase(Cell.Value) = "TOOL" Then
Cell.EntireRow.Select
Selection.Copy
Sheets("Sheet2").Select
Range("A1").Select
ActiveCell.SpecialCells(xlLastCell).Select
Selection.End(xlUp).Offset(1, 0).Select
Selection.EntireRow.Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Application.CutCopyMode = False
End If
Next Cell
End Sub


--
Mallycat
------------------------------------------------------------------------
Mallycat's Profile: http://www.excelforum.com/member.php...o&userid=35514
View this thread: http://www.excelforum.com/showthread...hreadid=555185

Find and copy in Excel
 

thanks Jamie but the macro is stopping at
If InStr(UCase(Sheets(SourceTab).Cells(x, "B")),

any suggestions?


--
TonyG001
------------------------------------------------------------------------
TonyG001's Profile: http://www.excelforum.com/member.php...o&userid=35737
View this thread: http://www.excelforum.com/showthread...hreadid=555185

Find and copy in Excel
 

Hi Millycat

I tried your code and it does not stop but it seems that nothing is
being pasted in my destination sheet.


--
TonyG001
------------------------------------------------------------------------
TonyG001's Profile: http://www.excelforum.com/member.php...o&userid=35737
View this thread: http://www.excelforum.com/showthread...hreadid=555185

Find and copy in Excel
 

Yep, try mine:)


--
Mallycat
------------------------------------------------------------------------
Mallycat's Profile: http://www.excelforum.com/member.php...o&userid=35514
View this thread: http://www.excelforum.com/showthread...hreadid=555185

Find and copy in Excel
 

It should work. Just make sure you have the whole line on there right
(pasting code on here sometimes breaks the lines at weird spaces. That
entire line ends with the "then".

I've never seen anyone unable to run the instr or ucase functions.

Did you rename the variables to match your workbook? i.e "sourcetab"
and "desttab" etc.
TonyG001 wrote:
thanks Jamie but the macro is stopping at
If InStr(UCase(Sheets(SourceTab).Cells(x, "B")),

any suggestions?


--
TonyG001
------------------------------------------------------------------------
TonyG001's Profile: http://www.excelforum.com/member.php...o&userid=35737
View this thread: http://www.excelforum.com/showthread...hreadid=555185

Find and copy in Excel
 

you can also try the Find method (maybe faster)

Sub SearchAndCopy()

Dim varFind

Dim lngFoundRow As Long
Dim lngFirstRow As Long

Dim lngA As Long

Dim strSearch As String
Dim rngA As Range

strSearch = "TOOL"
lngA = Worksheets("destination").Cells(1, 1).CurrentRegion.Rows.Count +
1

With Worksheets("source")
Set varFind = .Columns(2).Find(strSearch, _
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If Not varFind Is Nothing Then
lngFirstRow = varFind.Row
Do
For Each rngA In .Rows(varFind.Row).Cells
Worksheets("destination").Cells(lngA,
rngA.Column).Value = .Cells(varFind.Row, rngA.Column).Value
Next rngA
Set varFind = .Columns(2).FindNext(varFind)
lngFoundRow = varFind.Row
lngA = lngA + 1
Loop While Not varFind Is Nothing And lngFoundRow <
lngFirstRow
End If
End With

End Sub

Kind regards,

hugo de wilde


--
H.A. de Wilde
------------------------------------------------------------------------
H.A. de Wilde's Profile: http://www.excelforum.com/member.php...o&userid=30679
View this thread: http://www.excelforum.com/showthread...hreadid=555185

Find and copy in Excel
 

Thanks Hugo. Your code worked fine for me I also added two lines that
will first clear the sheet before pasting


--
TonyG001
------------------------------------------------------------------------
TonyG001's Profile: http://www.excelforum.com/member.php...o&userid=35737
View this thread: http://www.excelforum.com/showthread...hreadid=555185

Find and copy in Excel
 

See
http://www.rondebruin.nl/copy5.htm


--
Regards Ron De Bruin
http://www.rondebruin.nl



"TonyG001" wrote in message
...

I need a macro that will search for all occurances of the word "tool" in
col B of a spreadsheet and then copy the entire row of each occurance
and paste the rows in another worksheet within the same spreadsheet


--
TonyG001
------------------------------------------------------------------------
TonyG001's Profile: http://www.excelforum.com/member.php...o&userid=35737
View this thread: http://www.excelforum.com/showthread...hreadid=555185