saetamortal
New Member
- Joined
- Jan 4, 2012
- Messages
- 4
MasterSheet1 Range B:B has unique values like ID1, ID2, ID10000, etc.
There are 20 more sheets, named consecutively sheet2, sheet3,.. , sheet21. Each sheet represents a unique exclusive filter criteria, like sheet2 are all ID´s of items that are color "blue" and/or sheet3 has all the ID of green items. Items have only one color, so no ID should be repeated inside some sheet values and, more important, ID cannot be repeated across any of the 20 sheets.
Each of the 20 sheets has Range B:B containing a sub set (about 1000) of those Master sheet ID´s, since it is the origin of all ID´s.
I need a VBA button that prints on a new sheet, called reportsheet the following data columns:
MASTERIDTESTED (All values contained in mastertable should appear here), LOCATION (Full path address external), MATCHNUM (times the ID was found on any of the 20 sheets).
It is expected that one report record looks like the following:
If there is no match (meaning that particular ID has a color that is not on any of the 20 sheet) :
ID5, MasterSheet1!B5, matchnum 0.
If there is only one match one record is to be found:
ID6, Sheet18!B998, matchnum 1.
If there is more than one match, multiple records are to be found:
ID6, Sheet17!B605, matchnum 1.
ID6, Sheet18!B998, matchnum 2.
ID6, Sheet19!B300, matchnum 3.
So that is basically the code that you can enlighten me with
There are 20 more sheets, named consecutively sheet2, sheet3,.. , sheet21. Each sheet represents a unique exclusive filter criteria, like sheet2 are all ID´s of items that are color "blue" and/or sheet3 has all the ID of green items. Items have only one color, so no ID should be repeated inside some sheet values and, more important, ID cannot be repeated across any of the 20 sheets.
Each of the 20 sheets has Range B:B containing a sub set (about 1000) of those Master sheet ID´s, since it is the origin of all ID´s.
I need a VBA button that prints on a new sheet, called reportsheet the following data columns:
MASTERIDTESTED (All values contained in mastertable should appear here), LOCATION (Full path address external), MATCHNUM (times the ID was found on any of the 20 sheets).
It is expected that one report record looks like the following:
If there is no match (meaning that particular ID has a color that is not on any of the 20 sheet) :
ID5, MasterSheet1!B5, matchnum 0.
If there is only one match one record is to be found:
ID6, Sheet18!B998, matchnum 1.
If there is more than one match, multiple records are to be found:
ID6, Sheet17!B605, matchnum 1.
ID6, Sheet18!B998, matchnum 2.
ID6, Sheet19!B300, matchnum 3.
So that is basically the code that you can enlighten me with
Last edited:
