getting data from many sheets into one

[Lisa870523]

For anyone who can help me:

I have an Excel file with many (over 30) spreadsheets. All are formatted exactly the same, just holding different information. What I need to do is have a macros that goes through each of these sheets and takes the given information that I need. But I don't know how to write this.

The information I need is in cells B17, D17, F17, I17, K17, and M17. Preferably, this information just gets loaded into a new sheet in the document, say MAIN, in each column. So, B17 goes in A1, D17 goes in A2, etc. From there, each column is creating a line on a graph, which is also a separate sheet, say GRAPH. Everytime a new column is added in MAIN, I need the GRAPH sheet to update as well. This is a whole new issue, too.

If you need any clarifications, please let me know. And thank you for your help!

[VBA Noob]

Lisa870523,

Welcome to the forum. Please take a few mins to read our forum rules below and then add the link to the other excel forum as cross posting without the link is not permitted

VBA Noob

[MatrixMan]

Hi Lisa ... I think this is what you're after ... If you add it into an appropriate event module (like Worksheet Change or Select or possibly only on Chart Activate) then it will generate a new main page with your data and set that data for the chart.

Please Login or Register  to view this content.

As a general observation, I'd suggest that you find a better way to identify the cells in each sheet you want to sum; something about those cells that is unique, such as whether they are the only subtotals, or have coloured backgrounds, or have a text in the cell to their left like "Total =". But, if you're sure these cells never change, then I guess you can leave it as it is.

Hope this helps. MM.
Noli nothis permittere te terere.