Sorting and copying data to another worksheet in a workbook.

[Guest]

I have created a workbook with multiple worksheets, one for each month of the
year.
Each worksheet has four columes: DESCRIPTION, DATE, AMOUNT, TYPE

Example: JAN (worksheet name)
DESCRIPTION DATE AMOUNT TYPE
Maple St. 6/1/06 $1200.00 Check
Davis St. 6/2/06 $1300.00 Credit
Wilson St. 6/1/06 $ 900.00 Cash
Brown Ave. 6/4/06 $2250.00 Credit
Mare St. 6/1/06 $1150.00 Money Order
Sikes Dr. 6/10/06 $1000.00 Credit

At the end of each month, the data needs to be copied, arranged by date,
sorted by TYPE and moved to their respective summary worksheets (Check
Transactions, Credit Transactions, Cash Transactions and Money Order
Transactions).

Example of results:

Check Transactions (worksheet)
DESCRIPTION DATE AMOUNT
Maple St. 6/1/06 $1200.00

Credit Transactions (worksheet)
DESCRIPTION DATE AMOUNT
Davis St. 6/2/06 $1300.00
Brown Ave. 6/4/06 $2250.00
Sikes Dr. 6/10/06 $1000.00

Cash Transactions (worksheet)
DESCRIPTION DATE AMOUNT
Wilson St. 6/1/06 $ 900.00

Money Order Transactions (worksheet)
DESCRIPTION DATE AMOUNT
Mare St. 6/1/06 $1150.00

I’m fairly new to complex formulas, so any help would be appreciated.
Thank you.

 

[mrice]

The simplest way to do this is with autofilters, copying the rows for
each type to the summary sheet. As you are only doing this once a month
this shouldn't take too much time. You could go for an automated
solution with macros but this is maybe over the top.

 

[Guest]

I appreciate the input. However, this was a small sampling of data. The
actual data is thousands of lines long with over 20 different summary sheets
for over 100 different worksheets. I have been cutting and pasting thus far
and it takes me at least a weeks worth of work to complete each month. An
automated solution will save tens of thousands of dollars over the course of
a year. Thank you again for the suggestion.
With respect,
Geo

 

[mrice]

In this case a macro is your answer.

If you have a list of your 100+ workbooks in a spreadsheet or they sit
in a small number of folders you can loop through them opening one by
one looking for the data relevant to each summary sheet.

Try recording one and see how you get on. Using the dir() function will
enable you to extract filenames from folders - see the Help files for
details.

You might also consider getting your data into a database which would
make this sort of summarisation so much easier.