Summary sheet to refer to cell on multiple sheets (always same column, but row# can vary)

Hi Everybody,

I found some code from Ron de Bruin that creates a Summary sheet form all worksheets. Of course, it works perfectly, but I want to modify it slightly to meet my needs. In his example code, he has it referring to:

Please Login or Register  to view this content.

I tried modifying to point to a different range, but it throws an error. Here is the modification I made:

Please Login or Register  to view this content.

My goal with the above code is to refer to the cell two rows below the last cell with continuous data in column M on every sheet in the workbook.

Here is the entire sample code provided by Ron without modification:

Please Login or Register  to view this content.

All help is greatly appreciated!

Carlos

i think this is all you need:

Please Login or Register  to view this content.

the for each myCell loop is not usefull when refering to only one cell in a sheet.

Hi SjonR, and thank you for your reply, but it does not achieve what I am trying to do.

I have several project sheets with data (about 30 or so sheets). The code that I found on Ron de Bruin's site creates a summary sheet for the data in all of the other project sheets. This is perfect. Next, I want to retrieve data from some cells in all of the project sheets to present on the new summary sheet. In Ron's sample code, he points to ("A1,D5:E5,Z10"), and it works perfectly. Unfortunately, those cells are not the ones that I want to reference on my project sheets. Instead, I want the code to point to the cell two rows below the last cell with continuous data in column M on every project sheet in the workbook.

Each project sheet has various values in column M. So, it might look like: Header = Backlog, cell M2 contains 4, cell M3 contains 5, and then M4 is blank, and M5 contains the sum of the values in M2 and M3 which = 9. The number of rows varies from sheet to sheet, so the sum of values in column M on the next sheet might sit in cell M22, and the sheet after that, the sum might sit in cell M18.

I want the code to find the sum of the values for column M on each sheet, and return that value to my summary sheet.

An example file Will help us a lot.

Sorry for the delayed response ... I cannot reply to posts on this forum (even though I can create new posts!)

I played around with the code some more, and I was able to make it work. Here it is:

Please Login or Register  to view this content.