How to generate report on rows from multiple worksheets where column has particular text


New Member
Feb 26, 2016
HELP!! I've been struggling for a few days with this issue now and although i've come across a few different examples which i think sound similar to what i need my to worksheet to do, given that i'm pretty much a complete novice at VBA, i cant work out how to adapt the examples i've found to suit my issue. My issue is this:

I basically have 3 separate worksheets (Team 1, Team 2 & Team 3) which have the same column headers which are: 'Grade', 'Trade', 'Employee No', 'Surname', ' Forename/s', 'Shift', 'Number of courses present', 'All essentials present', 'Percentage against grade', 'Essentials not present', 'Additionals', and 'Essentials required for grade' which are in row 1 of columns A to L respectively. There is then also a separate 'report tab' within the same workbook.

What i want is to have two buttons on the 'report' tab which i can assign a macro to each, which will effectively produce a report by filtering column 'H' in each of the three team sheets to just the entries which have the text "No" in them, and copy them over to the 'report' sheet. Likewise i would like a separate button which does the same for the entries with "yes" in column 'H'. However, due to the document being updated regularly, i would like the buttons to perform the action of updating the data, so if for example one of the entries goes from a "No" to a "Yes" in column 'H' then, upon clicking the button again, the information will update and that entry will be located under the correct report.

I hope i've explained as best as possible, any help with coding if necessary or any other solution for that matter would be hugely appreciated.



Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.


New Member
Sep 12, 2015
Hi Liam,

To me it would be simpler to have all teams in the one sheet. Just insert a column for the Team Number.

Then you can just filter on Team Number and column H with Yes or No etc.


Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...