Selecting active area on sheets with validation lists and conditional formatting...

Hi All,

I have a workbook with 20 sheets out of which 3 are named “Country”, “Region” and “Sub Region”. The remaining 17 sheets contain feedback from users from various countries and regions.

The design of the 17 sheets is as follows:
Users enter their names, countries, regions and sub regions via a validation list in cells D4-D7. They then move on to fill several rows of data (Range “B9:N9”). Some of these are selected via a validation list to reduce input error. I have copied the validation lists and formats from B9:N9 to B109:N109. Users don’t fill 100 entries, but I copied the formats on 100 rows to be on the safe side.

The Country, Region and Sub Region sheets are for summary purposes. A user selects a geographical location, and a macro loops through the 17 sheets and copies entries from “USA” for example.
The problem I’m having is that the macros I currently have copies from Ranges B9:N9 to Ranges B109:N109 onto the destination sheet, so even though I have the results I want, I have empty rows between results.

Please HELP!

Please Login or Register  to view this content.

I’ve been told to use a “First Row” and “Last Row” to declare the active area on each sheet before copying them over onto the destination sheet. However, my beginner VBA skills limit me from doing that…

Please help!!

Begin with not using select or activate in VBA.

use

Please Login or Register  to view this content.

Instead of

Please Login or Register  to view this content.

and use

Please Login or Register  to view this content.

instead of

Please Login or Register  to view this content.

Is this what you mean?

Please Login or Register  to view this content.

Is the message box meant to show for every sheet that has no relevant records?
I'm not sure what the last if statement is meant to achieve

Try this

Please Login or Register  to view this content.

Providing a workbook always makes you problem clearer, and usually ensures a faster solution to your problem.

Hope this helps