Excel - create vba macro - find cell containing certain word copy cell content paste to another worksheet

Posted on 2006-03-24
Medium Priority
Last Modified: 2012-06-27
I would like to search an excel file for a certain word -> for each cell found -> copy that cell's content and paste it in another worksheet.
-> Make a list of the extracted content. (that is 2nd copy and paste will be placed below the 1st extracted cell).

Question by:zaza5586
1 Comment
LVL 35

Accepted Solution

mvidas earned 120 total points
ID: 16283943
Hi zaza,

First off, take a look at http://vbaexpress.com/kb/getarticle.php?kb_id=195 a useful find routine made by mdmackillop (also on EE occasionally), should probably do exactly as you need.

You can also use the following:

Sub zaza5586()
 Dim WS As Worksheet, nWS As Worksheet, vWhat As String, CLL As Range, FND As Range
 vWhat = InputBox("What are you searching for?")
 If Len(vWhat) = 0 Then Exit Sub
 Application.ScreenUpdating = False
 Set nWS = Sheets.Add
 On Error Resume Next
 nWS.Name = "Search Results"
 On Error GoTo 0
 For Each WS In ActiveWorkbook.Sheets
  If WS.Name <> nWS.Name Then
   Set FND = Nothing
   Set FND = FoundRange(WS.Cells, vWhat)
   If Not FND Is Nothing Then
    For Each CLL In FND.Cells
     With WS.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
      .Value = CLL.Value
      .Offset(0, 1).Value = "'" & WS.Name & "'!" & CLL.Address(0, 0)
     End With
   End If
  End If
 Application.ScreenUpdating = True
End Sub
Function FoundRange(ByVal vRG As Range, ByVal vVal) As Range
 Dim FND As Range, FND1 As Range
 Set FND = vRG.Find(vVal, LookIn:=xlValues, LookAt:=xlPart)
 If Not FND Is Nothing Then
  Set FoundRange = FND
  Set FND1 = FND
  Set FND = vRG.FindNext(FND)
  Do Until FND.Address = FND1.Address
   Set FoundRange = Union(FoundRange, FND)
   Set FND = vRG.FindNext(FND)
 End If
End Function


Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The core idea of this article is to make you acquainted with the best way in which you can export Exchange mailbox to PST format.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question