Combine Data from multiple sheets

Hi

I dont know if the topic is what its supposed to be but I need some help.

From our 'System' we retrieve a report for say 10 clients, and the data is produced in one Excel file, but in 10 different worksheets. Data is as follows.

Doc Nr Date Rental Ref Driver Na Debit
INV: 112 27/02/2009 18 566556656 John 100.00
INV: 110 27/02/2009 26 135568688 Mark 150.00
INV: 100 27/02/2009 51 254123698 Paul 125.00

What I want is to put all the data from all the 10 sheets, in one sheet underneath each other. So i end up with one worksheet with all the information on it. Can anyone help? I've tried using combine / pivot though its not working.

Many Thanks
Sam Farrugia

Hi, Try the code in the attached file.
NB:- Change "Client A" at the top of the code to reflect the Consolidation Sheet name.
NB:- See Remark in Code, Reference Starting Row 1 or 2.
Regards Mick

Thats incredible... works wonders. Now if I may ask for some additional info?

1. When I tried to 'Move or Copy' Sheet A to a new extracted file it did not allow me. Is it because there is a macro? How can I get around it? Else, tell me how to copy and paste the code in the editor.

2. The sheet names are not 'Client A', 'Client B' etc.. but they have the actual names; example 'HSBC', 'Barclays' etc. Now I have a list of code numbers linked to each account name, which is on a spreadsheet in two coloumns in the format....

1 HSBC
2 Barclays

When transactions are copied from sheet 'HSBC' to the Main Sheet, is it possible to have a new coloumn created and have the code inserted. Perhaps via a 'Vlookup'?

_____

For better understanding lets assume that the sheet were I want the data to be displayed is called 'SheetA' and the code numbers are on 'SheetB'

Thanks
Sam

Managed to sort out the Move and Copy function.. its coz the files were not saved..

just the other one then.. thanks
sam

Hi, I can't understand why you can't copy "Sheet A". The Macro should have no Effect. !!
The new code attached ,has a new sheet "Main" to copy all Data to,
and a Sheet "Sheet B" that has your code Names and Banks (Column "A & B").

Bank Names and Codes Now appear in column "I".
You could remove the Bank Names (Range("I" & Last) = Ws.Name), from the code if not wanted.
Regards Mick

Hi.

I sorted out the Move and Copy. Thanks.

See attached the file again. Just need one more change that the account number is on all the rows.

Thanks
Sam

Hi, Try this:-
Regards Mick

MickG.. That is brilliant!! Thanks alot. You have saved us loads of time with your coding.

Regards
Sam Farrugia