Export data from multiple sheets into a new sheet

[snoopyngd]

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.

[Gti182]

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.

hey mate have you tried the macro recorder function? I'm doing something similar on this post: http://www.excelforum.com/excel-prog...-recorder.html

are you trying you trying to paste many sheets data into one sheet? If so i read about using the consolidating function. Data, Consolidate may help.

[snoopyngd]

Hi,

Thanks for reply. I tried recording, but the problem is I have varying number of sheets in different workbooks. But I ned the Macro to do the same thing on all of them.
So I want it to gather the data from the same cells and create a new sheet with the data stacked, but the number of sheets is unknown.

[JBeaucaire]

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.

[snoopyngd]

Jerry,

Thanks for reply, that was helpful.

Using your code for a single workbook to copy data from worksheets into the Report sheet, I found that the data is copied into the same cells. So data from different sheets is copied into the same cells. And I wanted the data from the first sheet to be copied into the Report sheet, then the data from the Second sheet to be copied to the cells below and etc.?

[JBeaucaire]

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.

[snoopyngd]

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.

[JBeaucaire]

Try this:

Please Login or Register  to view this content.

[JBeaucaire]

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

[snoopyngd]

Thanks very much Jerry, you are trully a GURU!

[snoopyngd]

Jerry,

Hoep you 're fine. Can you help me out with the code for the same example as above, but now I need to copy all of the data including the Total values?

[JBeaucaire]

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)

[snoopyngd]

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)

I see what you mean. But the problem is that I might be given data without the Total column or with correct values and the code doesn't work in that case.

[JBeaucaire]

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.

[snoopyngd]

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.

Previous macro works perfect! It is the fact that I might be asked to include the Total column in the summary that makes a problem. That why I would like to have a macro for two different cases. And I can generate the correct Total column anytime.

[JBeaucaire]

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.

[snoopyngd]

Wow great job! It works perfectly! Thanks Jerry!