EXCEL find-copy-paste
Home > IT Answers > Development > EXCEL find-copy-paste
Snursmursik
5 pts.
0
Q:
EXCEL find-copy-paste
VBA, Microsoft Excel
Hello! Using VBA for first time, so everything is toughed for me.. The problem is: we need to find on the first sheet named SD in second column all cells contianing word 'drilling', then for each cell copy row which contains that cell and paste it in other sheet or book (doesn't matter). Please help me, the work is urgent and my code doesn't work

Dim lCount As Long
Dim rFoundCell As Range

Sheet("SD").Activate

Set rFoundCell = Range("B2")

For lCount = 1 To WorksheetFunction.CountIf(Columns(2), "drilling")
Set rFoundCell = Columns(2).Find(What:="drilling", After:=rFoundCell, _
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, MatchCase:=False)

With rFoundCell
.Select.Row
.Selection.Copy
Windows("Book1").Activate
Columns("'lcount'").Select
ActiveSheet.Paste
Windows("SD").Activate
End With

Next lCount
End Sub
ASKED: Jul 16 2008  6:50 AM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
RELATED QUESTIONS
0
JP2112
475 pts.
0
A:
 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0
  • AddThis Social Bookmark Button
Better late than never.

Why can't you just manually filter the data on that column? Anyway, here's a sub that should work. Untested.

Sub LoopThroughColumn()

Dim lCount As Long
Dim rFoundCell As Range
Dim MyBook As Excel.Workbook
Dim NextRow As Long

Set MyBook = Workbooks("Book1")

NextRow = WorksheetFunction.CountA(MyBook.Sheets(1).Range("B:B"))

Worksheets("SD").Activate

Set rFoundCell = Worksheets("SD").Range("B2")

For lCount = 1 To WorksheetFunction.CountIf(Columns(2), "drilling")
Set rFoundCell = Columns(2).Find(What:="drilling", After:=rFoundCell, _
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, MatchCase:=False)

rFoundCell.EntireRow.Copy MyBook.Sheets(1).Range("A1").Offset(NextRow, 0)

Next lCount

End Sub
Last Answered: Aug 30 2008  2:17 AM GMT by JP2112   475 pts.
  •   
0
0
RSS - Discussions Help
Discuss This Answer:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

EXCEL find-copy-paste - Learn Excel   0 pts.  |   Jul 24 2008  9:16AM GMT

[...] Original post by unknown [...]

 
0