Extracting Row in multiple tabs by keyword

[Leogarcia1988ha]

Hello I'm new here. My name is Leo.

I have a database of keywords in diffrent tabs and would like to know if there is a way to pull information out of this tabs and display it.

i.e.

I would like all the rows that contain the word "fish" in all the tabs to display on "this tab"

I have been trying to do it for one tab...

HLOOKUP(fish;A5:E104; )

No success though.

Can someone please let me know how to do this.

Thank you

 

[mumps]

Is the word "fish" always in a particular column in each sheet or can it be anywhere on the sheet?

 

[Leogarcia1988ha]

Hello Mumps.

Here are the column titles

<colgroup><col width="256"><col width="172"><col width="86"><col width="86"><col width="86"><col width="86"></colgroup> <tbody>
[TD="width: 256"] Keyword [/TD]
[TD="width: 172"]Search Volume[/TD]
[TD="width: 86"]CPC[/TD]
[TD="width: 86"]Competition[/TD]
[TD="width: 86"]Number of Results[/TD]
[TD="width: 86"][/TD]

</tbody>

So yes. Column A has the "keyword" always

 

[mumps]

Try this macro. Do the following: Hold down the ALT key and press the F11 key. This will open the Visual Basic Editor. In the menu at the top click 'Insert' and then click 'Module'. Copy and paste the macro into the empty code window that opens up. I have named the destination sheet as "Summary" so you must make sure that you have an empty sheet with this name before you run the macro. Alternatively, you can change the name in the macro to match yours. Press the F5 key to run the macro. Close the code module window to return to your sheet. There are other quicker ways to run the macro such as assigning it to a button that you would click on your sheet or assigning it to a short cut key.

Sub CopyRows()
    Application.ScreenUpdating = False
    Dim foundVal As Range
    Dim sAddr As String
    Dim ws As Worksheet
    For Each ws In Sheets
        If ws.Name <> "Summary" Then
            Set foundVal = ws.Range("A:A").Find("fish", LookIn:=xlValues, lookat:=xlWhole)
            If Not foundVal Is Nothing Then
                sAddr = foundVal.Address
                Do
                    foundVal.EntireRow.Copy Sheets("Summary").Cells(ws.Rows.Count, "A").End(xlUp).Offset(1, 0)
                    Set foundVal = ws.Range("A:A").FindNext(foundVal)
                Loop While foundVal.Address <> sAddr
                sAddr = ""
            End If
        End If
    Next ws
    Application.ScreenUpdating = True
End Sub

I also assumed the word "fish" is the only word in the cell in column A and not part of a group of words in the cell. If "fish" is one word in a phrase in a cell, then change "xlWhole" in the code to "xlPart".

 

[Leogarcia1988ha]

Wow. This is waaaaay above my pay grade

I change Whole to Part

When I tried to run it with f5 I get a "basic syntaz error"

 

[mumps]

When you get the error and click 'Debug', which line of code is highlighted?

 

[Leogarcia1988ha]

Dim foundVal As Range

 

[mumps]

I tried the macro on some dummy data and it worked properly without any errors. It is always easier to help and test possible solutions if we could work with your actual file. Perhaps you could upload a copy of your file to a free site such as www.box.com. or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.