Match several values in ‘Summary Sheet’ to several values in ‘Mast

[ryguy7272]

I found the Code below, on a post at this very DG, a while back:
Sub stance()
Dim myrange, copyrange As Range
Sheets("Mapping AM").Select
Set myrange = Range("E2:E200")
For Each c In myrange
If c.Value <> "" Then
If copyrange Is Nothing Then
Set copyrange = c.EntireRow
Else
Set copyrange = Union(copyrange, c.EntireRow)
End If

End If
Next
copyrange.Copy
Sheets("Summary Sheet").Select 'Change to suit
Cells(1, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues
End Sub

I will put an ‘x’ in Column E of sheet ‘Mapping AM’ and run the code.

This gets me close to where I would like to be. The next step, and I think
final step…I hope…is to take the values in the ‘Summary Sheet’, in Column B,
and find matches in another sheet, named ‘Master’, and somehow tie the items
in ‘Summary Sheet’ to the items in ‘Master’. I guess, some kind of cascade
would make sense. I’m kind of stuck on how to display the final results, so
I am open to suggestions, and definitely open to help about how to match
values in ‘Summary Sheet’ to ‘Master’. I guess Index/Match may work.
Perhaps filter the column, if the values on the two sheets match. Something
along those lines should get me pretty close to being done with this project.
Finally, I would like to copy the entire row in ‘Master’ if the items in
‘Master’ match the items in ‘Summary Sheet’.

The thing that makes this difficult is that there may be one value, like
‘Lee’ and several salespeople report to ‘Lee’. I want to copy/paste these
salespeople into a hierarchy-type table, maybe inserting a variable number of
rows, to show the relationships between ‘Lee’ and his sales staff. Vlookup
won’t do what I need to do.

Thanks,
Ryan---

 

[Barb Reinhardt]

This doesn't answer your question, but I just read something today about this

Dim myrange, copyrange As Range

translates to
Dim myrange as Variant
Dim copyrange As Range

Is that really what you want?

 

[JLGWhiz]

Hi B arb, You are correct. To be efficient the Dim should always specify
the As for each variable in VBA although some scripters get in the habit of
not using As since all Script variables are vaiant data types. However, in
this case he later uses the Set statement which pretty well ties it down as
an Object variable.

 

[JLGWhiz]

Database design is 95% of any sizable project. If you look at some of the
tutorials for Pivot tables, you will see how a well laid out database lends
itself to not only data manipulation, but also logical grouping of data
source, etc. Code writing is simplified when the writer does not have to use
exotic code to accomplish their goal. In summary, organizing your data
properly makes it a lot easier.

 

[ryguy7272]

You were right, JLGWhiz! When I sat down and REALLY thought about my needs
for this project, I was able to rework some things and eventually come up
with a solution. Everything is working now!! Thanks for the push!!

Regards,
Ryan---