Combining data from various sheets, with different column order, into a new sheet.

I have created a payment workbook for the membership organisation/charity I work for. We take membership fees by various methods - direct debit (DD), PayPal, from local groups, and some other methods. For DDs we get various reports each month - paid, unpaid, cancelled, new donor. So I now have a workbook with 6 sheets for DD paid, DD unpaid, DD cancelled, DD New Donor, PayPal, Group payments. All payments come from a specific member, and each member has a unique membership number, and the membership number (a simple 5 digits) is listed in a column on every sheet.

What I want to do is to create a 7th sheet that automatically contains every line of data in those 6 sheets, but in a standard column layout. So, for e.g., the column layout might be:

Mem#,date,amount,frequency,paymenttype

For the DD paid sheet, the above might represent:

Column#3,column#15,column#14,column#20,

but for the PayPal sheet, it might represent:

column#3,column#4,column#8,column#12

The data in this new sheet would need to update automatically when new data is entered into the other sheets (we get monthly reports for all the DD stuff) and would ideally be contained in a table, as I want to be able to filter the mem# column to see a full payment history.

Also, I may link this new sheet to a table in our membership Access database so that the full payment history will show on each member's 'page'.

Ideas?

TIA!