R
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---
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---