Index and Match

[Tennisguuy]

I think this can be done in excel but not sure how to accomplish. I have a list of accounts with various data. What I would like to do is move certain accounts and the data associated with them from this list into another sheet. I want the data that is moved to appear in another list form.

For example-list of the account

Name of Account Eff Date Broker Rep Code
ACME Hardware 1/1/2011 ABC Agency Smith 1
Howard Mills 2/1/2001 Johnson LTD Lewis 3
Water Mills 1/15/2011 ABC Agency Black 1
Regal Furniture 3/2/2011 Willis Inc Brian 2


What I would like to do is move any account with a code 1 into a new sheet. I want to move the entire row of data except for the code. In this example anything coded with 1 I would want to move. That would mean the ACME Hardware and Water Mills. Again I would want to move the entire row.

The list of data I have is about 200 rows this is just a sample. Once I move it to another sheet I want to keep it in list form.

Yes I know I can just sort by the code column and just then copy and paste but I don't want to have to do that everytime because the data is 200 lines now and growing.

Thanks,

 

[John Davis]

I think this can be done in excel but not sure how to accomplish. I have a list of accounts with various data. What I would like to do is move certain accounts and the data associated with them from this list into another sheet. I want the data that is moved to appear in another list form.

For example-list of the account

Name of Account Eff Date Broker Rep Code
ACME Hardware 1/1/2011 ABC Agency Smith 1
Howard Mills 2/1/2001 Johnson LTD Lewis 3
Water Mills 1/15/2011 ABC Agency Black 1
Regal Furniture 3/2/2011 Willis Inc Brian 2


What I would like to do is move any account with a code 1 into a new sheet. I want to move the entire row of data except for the code. In this example anything coded with 1 I would want to move. That would mean the ACME Hardware and Water Mills. Again I would want to move the entire row.

The list of data I have is about 200 rows this is just a sample. Once I move it to another sheet I want to keep it in list form.

Yes I know I can just sort by the code column and just then copy and paste but I don't want to have to do that everytime because the data is 200 lines now and growing.

Thanks,

Can this help?

Sub Tennisguuy()
Dim lr As Long

lr = Cells(Rows.Count, 1).End(xlUp).Row

x = InputBox("Please Enter the Code")

Columns("E:E").AutoFilter
Selection.AutoFilter Field:=1, Criteria1:=x
Range("E2:E" & lr).EntireRow.SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet3").Range("A1")
Columns("E:E").AutoFilter


End Sub