Getting Excel to copy from one sheet to the other based on Category.

[RoSe13]

I have this spreadsheet and on one sheet I have a list of assets, on another sheet I have 7 tables, one for savings, checking, investments, etc. I normally have to copy over all the assets and manually sort them by each category then add them to their respective table, since this is very time consuming, I want to be able to have excel do that automatically. I know I probably have to use a macro because it would be too many rules for an "if" equation right? I did some research and found something called an if then statement, but I am a little confused. Can someone explain how I can get this to be done automatically?
If it helps there is a column on the first sheet that tells you the asset type, so I was previously just filtering the type and copying and pasting, the only thing is that its not in the same order. For example on sheet 2 (with the tables) column B lists the Banking institution and Column D lists the account number, but on sheet 1 column A lists the banking institution and column D lists the account number.

 

[katekoz]

I turned your tables into actual tables, because I like how they autopopulate as the table grows. They're also nice when writing formulas because you don't limit the reference of your formulas. If you table gets to be 10,000 rows long, the formulas will still work!

I would start by adding one column to the end of your master table for each category and using a formula in those columns. These columns would autopopulate as you add more rows to your table, so once the formula is entered, you will never need to enter it again! As your table grows, the formulas will enter themselves and will work no matter how many or how few rows you ever have.

 

[katekoz]

Your second sheet's small tables will be made up completely of formulas (except for the "Account Name" field, as you said you manually enter that). The only thing you will ever need to do to these tables is enter the Account Name field and make them larger or smaller to encompass all of your rows. Here is what I would do for those:

 

[katekoz]

I put your Master table in Sheet1 and your little tables in Sheet2 for this file. The only thing is, I know you said you have 7 small tables/categories, and I only wrote these for 3. You'd need to add the additional 4 categories/tables and their formulas to your actual file.

 

[RoSe13]

I put your Master table in Sheet1 and your little tables in Sheet2 for this file. The only thing is, I know you said you have 7 small tables/categories, and I only wrote these for 3. You'd need to add the additional 4 categories/tables and their formulas to your actual file.

Sweet thanks so much! This actually works!