Export data from multiple sheets into a new sheet

Hi,

Can anyone help me out! I need to create a macro that will copy certain cells from different sheets and create a new sheet with all the data stacked.

You can declare the worksheet where all this data is supposed to collect so that you can refer to it anytime you want from any other workbook while the macro runs.

Please Login or Register  to view this content.

You can open all the workbooks in a single folder one at a time like so:

Please Login or Register  to view this content.

You can cycle through all the worksheets in the currently active workbook like so:

Please Login or Register  to view this content.

You can put all that together to open all the wbs in a single folder and add data row by row into your report sheet.

Please Login or Register  to view this content.

You didn't look closely enough at the FINAL macro where we introduced the NextRw variable. That's how we're controlling using only one row at a time, then incrementing to the nextrow each time we end the loop.

Check out the syntax of the For Each Ws section in that final macro, too. It uses that nextrow variable.

Jerry,

Thanks a lot for your replies! They really are helping in understanding the VBA.

I was trying to use your code on my Workbook and I am struggling to get the right result. Can you please review the files I've attached and help me with the code.
Basically what I need is a macro for Reports Monthly.xls file that creates a similar "Summary" worksheet as in Example.xls file. The sheet is a summary of data from other worksheets and stacks the data one on the other. The difference is that in Example.xls file the data is reported in years and in Reports Monthly.xls the data is monthly.
My problem was that the data has different number of columns in different sheets. And I need the data to be copied only until the "Total" column.

Try this:

Please Login or Register  to view this content.

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

I wouldn't include that column. The information in it is wrong. On the Brenda sheet, put this formula in FJ10 and copy down, you'll see what I mean:

=SUM($C10:FH10)

You mean the fact that the macro skips sheets without a TOTAL column doesn't work for you? I can change that pretty easily, but including the total column with bad data is a bad idea. Much better to simply ADD a correct TOTAL column on the summary when you're done.

This will process all sheets regardless of whether the TOTAL column is present, so no more sheets being skipped. It also automatically adds the correct totals in a column to the far right.

Please Login or Register  to view this content.