Combine multiple excel sheets of a workbook into a single sheet.

Hi!

I have a excel workbook containing more than 200 worksheets, which contain information in the same format. I would like to combine the sheets into a single worksheet such that the content A2:O29 of all the other sheets occurs below the content of sheet1 .

Can anyone help?

[Mohammad Monfared]

use 'xlsread' to read your desired data from sheets, concatenate them first in a single matrix or go straight to write theme using 'xlswrite' .

[avantika]

Hi!

I have a workbook containing multiple excel sheets , but xlsread only reads the first sheet . So how do you read the entire workbook?

[Mohammad Monfared]

to me using 'xlsread' is so simple with almost nothing to do yourself but writing few simple code lines. In 'xlsread' you can specify from which sheet you want to read data (I think in 2nd argument, see its documentation). so this could be done simply in a for loop for entire sheets.

Answer 1

[Image Analyst]

Don't use xlsread()! It will take forever (well, at least many minutes because you'd need to launch and shutdown Excel 200 times). It's best to use ActiveX , like in the demo attached below. If you also take a look at the Excel utility I wrote to transfer all worksheets in one workbook to another workbook, you'll be able to see how to copy a worksheet. So then all you need to do is to paste the copied worksheets into a worksheet where you keep track of what starting row you need to paste it in to. Not too hard, but might take a half hour of programming (by you) or so.

[avantika]

Hi!

Its too difficult for me to understand these codes..

Is there a simple way to do this?

[Image Analyst]

The simple way is to use xlsread, but it will take a very very long time. And you'd still have to keep track of what row to write into. If you run your program more than a few times, you'll definitely be seeing how you can learn activeX, which will be less time than waiting for the xlsread/xlswrite to finish. How long will it take to launch Excel 400 times and shutdown Excel 400 times on your computer? Because it will do that for every one of the 200 calls to xlsread or xlswrite. I bet you're smart enough to learn ActiveX. Heck if I did it, anyone can do it, or at least a smart engineer like you.