copy data in to different columns

Hi,
I have a workbook with multiple worksheets that all contain two columns of data with about 500 rows long. I wish to create vba code that will copy the data from all the work sheets and paste them in to the new worksheet whilst advancing the column location.

for instance the data in sheet1 is A500:B500 and the data in sheet 2 is also A500:B500. I want to copy data in sheet 1 to a new sheet at columns A:B and data in sheet 2 into the new sheet at C:D colums. so the macro needs to increment the column by two every time and then paste the data from the next work sheet. there are about 500 sheets, each with data in the same place.

hope this makes sence. look forward to your replys.

jon

Try this

Please Login or Register  to view this content.

hi,

thnaks for the code, it does work well. but instead of puting the data in a new work book, work it be easy to rearange the code so that a new sheet is created in the same workbook and the data is placed in this new sheet?

thank you, jon

This will add a new sheet

Please Login or Register  to view this content.