expand selection based on cell value

Sanibel

New Member
Joined
Mar 26, 2010
Messages
3
Hi, I have data sorted by column A, which are employee names. There are many line items for each employee name. I need to parse out the data by employee name - I would like to select all of the rows for one employee and cut and paste the data into another workbook. I can create a new workbook but am having difficulty defining the selection for each cut.

Can someone help me in defining the selection for each cut? I don't have much right now:

Dim Associate As String
Dim MyCell As Range

Do While Range("A2") <> ""
Range("A1").End(xlDown).Select
Associate = Selection.Text
For Each MyCell In Selection
If MyCell.Value = Associate Then


Thanks!
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,781
Filtering column A on the employee name would be the easiest way of gathering all of that employee's rows.
 

Sanibel

New Member
Joined
Mar 26, 2010
Messages
3
Thanks for the suggestion Mike, but I actually need this to be done automatically so various users can do this without having to go through all the motions or know excel really well.
I could really use some help in looping through a definition for each selection.
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,781
I'm suggesting using AutoFilter in your macro rather than a loop.
Record a macro of you doing what would be needed to do "Bob"'s records using AutoFilter and copy/paste with the last step being the turning of AutoFilter off.

Then that recorded macro can be modified to accept user input (from and InputBox? from a sheet's cell?) rather than the hard coded "Bob".
 

Sanibel

New Member
Joined
Mar 26, 2010
Messages
3
Ah, ok, thank you! The issue is, I need to be able to parse out the data for each employee, (a file will be created for eah employee with that employee's data) and this is a dynamic data set. So am still not sure if a filter would work in this case. Would you agree? Sorry for any confusion!
 

Watch MrExcel Video

Forum statistics

Threads
1,109,149
Messages
5,527,098
Members
409,745
Latest member
CharlesAlsop

This Week's Hot Topics

Top