Macro to copy rows from multiple worksheets based on criteria in range

Hi everyone,

Although I have been checking out this forum multiple times, this is my first post since I have not found was I was looking for in historic post. I'll try to describe what I am trying to do (I am quite new to VBA)...

I have a workbook with about 30 sheets (number is growing).

• One dashboard sheet (name = Dashboard)
• 30 country sheets that are all build up the same way (columns etc.) but with varying amounts of rows. Sheets are named by number (1-30)

• The macro will need to search all country sheets for the desired criteria
• The search criteria (person name) is selected by the user in an ActiveX control (drop down box), which is placed on the Dasboard sheet.
• The criteria could be in columns G:K in the country sheets
• When criteria is found, the rows with the corresponding criteria should be copied to the Dashboard sheet.
• The dashboard sheet has the first 5 rows with static data (incl. The ActiveX control etc), row 6 has the column names that are also on the country sheets so the copied data from the macro should be inserted to row 7 and below in the Dashboard sheet.

The search criteria can be on the same worksheet various times and all should be copied.

When a new name is selected in the drop down box, all existing data in the Dasboard sheet (starting row 7) should be replace by the information for the new selected contact person.

I hope I am a bit clear on what I am looking for and look forward to your expertise!

Thank you all for your help!

Example sheet final.xlsx

Post a sample workbook with at least 2 of these country sheets for reference and your Dashboard.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Your macros must be saved in another workbook because they are inaccessible in the one you posted. To continue, I at least need to know where the name gets placed after the user selects it from the dropdown box. what is the address of that cell?

Here's some code that looks at the dropdown list value as though it is placed in cell K1 on the sheet titled "Dashboard". Change "K1" in this code to whatever or wherever you intend to extract the search value. This code should loop through all sheets from the 3rd to the 2nd from the end, looking for the name chosen (and placed in K1 for lack of a better/correct place), and adding any line with the that name to the dashboard sheet.

Please Login or Register  to view this content.

Here's the same code, but instead of relying on the search value being placed in a cell first, it pulls the value directly out of the drop down box and searches for it from there.

Please Login or Register  to view this content.

Originally Posted by guidovdn

THanks! This one seems to work almost. However, if copies the same items sometimes multiple times, I can't find the error in the code.

Also, I have now 2 colums for contact persons (e.g. I and K). If I would put both names in one cell instead of 2 (e.g. both in I), can the macro than retrieve all cells containing the name searched for, although the second name in the cell does not match? So it would be a partly match.

Yes, the macro will do that automatically if you make that change. I haven't tested or looked at the data yet, but I suspect that the reason for the duplicate lines is any line with the same name appearing twice in separate columns. It would make no difference if you combine the names into one or not. Try changing this line:

Please Login or Register  to view this content.

To this:

Please Login or Register  to view this content.

This should add a check to see if the next instance of the found name is on the same row as the last, and if so, move on to the next row.

I just looked at the example you attached which I used to write this code, and it doesn't duplicate any lines for me, though it would have if there were duplicate names in the same line. In the example, there aren't any. I hope that extra Or in the if statement above does the trick. If not, post an example where it does duplicate some lines and I'll see what I can figure out. Also, I forgot to mention (in case you didn't already know) that you can apply this macro to the drop down box, so that as soon as a change is made in the box, it runs the macro instead of having to run it manually. To do this, right click on the box and choose "assign macro".

Try changing:

Please Login or Register  to view this content.

to this:

Please Login or Register  to view this content.

Also, try adding "Range("J1").Activate" after "Range("J:O").Select, like this:

Please Login or Register  to view this content.

I can see how that might be. Woops. Try changing to:

Please Login or Register  to view this content.

The only issue now left is that some results are shown multiple times...

That is very strange. That last code change should take care of that problem. One way to find out why this is happening would be to add the actual found address to a cell on your dashboard sheet so you can see where the duplicates are coming from.

After the code:

Please Login or Register  to view this content.

If column M is an okay place to add another value try adding:

Please Login or Register  to view this content.

Otherwise, choose a different unused column by changing Range("M" to Range("whatever you want". This will insert the address of the found value for each row in the Dashboard sheet. If the addresses are different (different rows) but the values seem duplicated, then there is no duplication, at least not as this programming is concerned, and the lines actually exist multiple times in the sheets where they were found. Otherwise, your intentions have been misunderstood by me... If there are duplicate addresses, then we have some other problem with the code.

Is there any chance you can create and upload a public friendly version of your workbook for me to try out? This code worked perfectly in the original version you posted, so there must be something about different about your actual workbook that I'm missing... In the meantime, can you post the entire macro as it exists in your workbook now?

So I went through the code changes as I suggested them and applied them to the original workbook you posted and found a few errors... The following is what I came up with to deal with those errors; maybe it will fix your problem outright. (I changed the G:K back to J:O in this code though, assuming that change was truly representative of your real workbook.)

Please Login or Register  to view this content.