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

[guidovdn]

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