Create macro to copy data using column headings

Hello,

I need a macro that will collect information from different worksheets and consolidates it on one tab.

In each worksheets, the number of columns is the same, but the number of rows differs. What I need to macro to do is to take the data + column headings from the first of the 5 source files and paste them into the final worksheet.

For example,

Worksheet 1

Ticker date cusip description ratings funds
aapl 2-Feb-13 aaaaaa apple aaa 1000
goog 15-Feb-13 bbbbbb google aaa 1500
yhoo 2-Mar-13 ccccccc yahoo AA 120
ko 1-May-13 dddddd coke BB+ 125
GM 12-Jul-13 eeeeee gen mtrs aa 1550


worksheet 2

date cusip description ratings funds Ticker
2-Feb-13 aaaaaa apple aaa 1000 AAPL
15-Feb-13 bbbbbb google aaa 1500 GOOG
2-Mar-13 ccccccc yahoo AA 120 YHOO
1-May-13 dddddd coke BB+ 125 KO
12-Jul-13 eeeeee gen mtrs aa 1550 GM



Now in worksheet 3, which will be the final worksheet, I want to create a macro which basially pulls in all the data from different worksheets and paste them in the following order.

Description Ticker ratings Funds Date Cusip



This example only has about 5 data sets, the actual file will have thousands of rows. Sheet 1 could have 1000, sheet 2 could have 500 and sheet 3 can have 1500. And this will wary on a monthly basis, which is why I don't want to copy and past the macro. This month I might have 1000 rows and next month I'll have 2000, so the macro will only pick up the 1000 not 2000, if I copy and paste.

The heading are the same in all the sheets. Can you please help me create a macro which will copy data from the different sheets using column heading and paste them into the my final sheet. So if I have a heading called "Ticker" i want the final sheet to grab all the information from each sheet under "ticker" and past them into the final sheet.

Also, each work sheet has names (sheet1=BNY sheet 2=UBS..etc..)

Can you please create a macro and show me where I can insert the names of the heading and sheets.


Thank you very much for all of your help. I hope I made sense.