Extracting Row in multiple tabs by keyword

Leogarcia1988ha

New Member
Joined
Jan 11, 2018
Messages
4
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
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Is the word "fish" always in a particular column in each sheet or can it be anywhere on the sheet?
 
Last edited:
Upvote 0
Hello Mumps.

Here are the column titles

KeywordSearch VolumeCPCCompetitionNumber of Results

<colgroup><col width="256"><col width="172"><col width="86"><col width="86"><col width="86"><col width="86"></colgroup> <tbody>
</tbody>

So yes. Column A has the "keyword" always
 
Upvote 0
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.
Code:
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".
 
Last edited:
Upvote 0
When you get the error and click 'Debug', which line of code is highlighted?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,993
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top