Macro to Search a List then write selective items to another sheet

[rwmill9716]

The sheet shown, Complaints, lists on each row information on a single complaint. This information extends from Col A to Col Y (though not that way here, the data actually begins in row 3). I want to highlight a Customer cell, say H227 for Customer A, then activate a macro that (1) recognizes the active customer (here A), searches the list, identifies each row where A is the customer and then writes each row into sheet "Event" beginning in row 4. I should then see the same A to Y cells of information, beginning in row 4, filled in sheet Event for the six customer A complaints. Note, the actual data in sheet Complaints involves 300 rows of data, and over 100 customers can be on the list, some up to 15 times. I know that I could do this by filtering the data, copying and pasting, but a simple macro would be quicker for the group of individuals involved. Note, I then use this information to populate a chart.

Thanks very much for your help,

ric

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
226	#	INITIATION Month	INITIATION DATE	CRM Transaction ID	Sales Order	Sales	SHIP Plant	CUSTOMER	CITY	ST/PROV	PRODUCT	Product	Lbs Involved	PROBLEM	Complaint Category	Complaint Sub Category	INITIATOR	INVESTIGATOR	STATUS	CARRIER	DATE CLOSED	DAYS TO CLOSE	Month Closed	Month Closed	Days Outstanding
227	1	Nov	11/09/12	5114885	301253256	C	CB	A	Sjarkaj	Dubai	NTA,POWDER,BAG25KG,40BAGS/P	NTA	44,000	Goods Infested with Termites	Pack-Out	Other	Msente	Larry Lavoie	Complete		1/8/2013	60	1	Jan	1
228	2	Dec	12/05/12	5115193	301260656	A	CB	B	Guangzhou	CHINA	NTA,POWDER,BAG,25KG,40BAGS/P,ASIA	NTA	2,200	Quality Issues	Quality	Product Performance	SSSU	J. Perkins	Complete		2/5/2013	62	2	Feb	2
229	3	Dec	12/11/12	5115325	30122111	B	Dec	B	Shanghai	China	INT,HMD,REFINED,100%,BULK,SOLID	HMD	3,000	Weight Discrepency	Pack-Out	Weight Discrepancy	DAMARR	Russell Husfeld	Complete		1/22/2013	42	1	Jan	3
230	4	Dec	12/12/12	5115332	301264681	A	CB	C	QB	Canada	NTA,POWDER,UNLINED	NTA	11,013	5 SS Not Shipped	Distrib/Warehouse	Picking & Packing	MBSCHE	Christine Iaconis	Complete		1/22/2013	41	1	Jan	4
231	5	Dec	12/12/12	5115343	301261675	A	Dec	D	Shanghai	China	INT,HMD,REFINED,100%,Bulk Solid	HMD	7,339	Low ISO Weights	Pack-Out	Weight Discrepancy	DAMARR	Russell Husfeld	Complete		1/22/2013	41	1	Jan	5
232	6	Jan	01/07/13	5115715	301260865	A	Dec	A		Germany	INT,HMD,Refined,100%,Bulk, Solid	HMD	40,393	Port date should be delivery date	Transportation	Delivery - Late	D.Marron	D. Marron	Complete		1/16/2013	9	1	Jan	6
233	7	Jan	01/08/13	5115760	301266808	A	Pns	A	Cincinnati	OH	Adipic, Resin, 258, FF, 2000lb, C-Bag, 45x45P	ADIPIC	44,000	Carrier ran out of hours	Transportation	Delivery - Late	W.Gummere	Tim Martin	Complete	CH Robinson	1/16/2013	8	1	Jan	7
234	8	Jan	01/09/13	5115783	301266743	A	Pns	A	Kingsport	Tn	Adipic, Resin, 220, FF, 2205lb, 1Bag/P	ADIPIC	88,200	Late deliveries (2)	Transportation	Delivery - Late	W.Gummere	Tim Martin	Complete	Heartland Express	1/29/2013	20	1	Jan	8
Complaints

 

[mumps]

Try:

Sub CopyRows()
    Application.ScreenUpdating = False
    Dim bottomH As Long
    bottomH = Range("H" & Rows.Count).End(xlUp).Row
    Dim customer As String
    Dim rng As Range
    Dim x As Long
    x = 4
    customer = InputBox("Please enter customer.")
    For Each rng In Range("H3:H" & bottomH)
        If rng = customer Then
            rng.EntireRow.Copy Sheets("Events").Cells(x, 1)
            x = x + 1
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub

 

[rwmill9716]

Thank you very much, Mumps. This works as desired, but I need one modification if possible.

What's happening now is when I activate the macro, a window pops up wanting me to type the customer's name, then it executes as desired. The problem is that some of these names are very involved (not the simple A, B C that I used in the example) so I need an easier way to enter the name. Could instead I copy the customer's name to cell A1 (in sheet Complaints), and the macro read that as the customer's name. Or even better, could I activate a particular cell with the customer's name (make that cell the active cell by clicking the mouse on that cell) in sheet Complaints and the macro take that as the customer that I'm interested in? In the latter case, the name could be taken anywhere in Column H.

 

[mumps]

Try this macro. First select the cell in column H and then run the macro.

Sub CopyRows()
    Application.ScreenUpdating = False
    Dim bottomH As Long
    bottomH = Range("H" & Rows.Count).End(xlUp).Row
    Dim rng As Range
    Dim x As Long
    x = 4
    For Each rng In Range("H3:H" & bottomH)
        If rng = ActiveCell.Value Then
            rng.EntireRow.Copy Sheets("Events").Cells(x, 1)
            x = x + 1
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub

 

[rwmill9716]

Mums,

That's perfect now. Thanks much.

ric

 

[mumps]

My pleasure!

 

[rwmill9716]

Mumps last macro above works well: Once a customer's name is highlighted, it scans the data set in tab "Complaints", and copies an entire row into tab "Events" for every row in "Complaints" that that customer's name appeared. For example, "Complaints" may have 200 entries with 15 associated with customer A. "Events" will then have 15 rows.

I need an adjustment for this macro that only copies and pastes A to Y columns and nothing beyond that as I want to do some computations with the customer's data in "Events."

Thanks again,

Ric

 

[mumps]

Hi Ric. Change this line:

 rng.EntireRow.Copy Sheets("Events").Cells(x, 1)

to this line:

 Range("A" & rng.Row & ":Y" & rng.Row).Copy Sheets("Events").Cells(x, 1)

 

[rwmill9716]

Thanks again, Mumps. Works great.

ric

 

[mumps]

Glad it worked out.