Consolidating data from several worksheets to one master sheet in excel 2010

Hello,
I am currently trying to consolidate information from several sheets (about 44) into one master sheet that will summarize the data. The template will be the same as the other sheets, but the information on the master sheet should be able to automatically update when new information is added on subsheets.

The workbook itself is not, in my opinion, formatted in the most user friendly/ appropriate way. The workbooks function is to be a place where several individuals can enter data for their particular state and we (the workbook administrators) keep track of the reports and its due date. I was given the task to find a way to consolidate the data into one master sheet (I will re-work the formating later).

I have searched several forums and many of them have either gone unanswered or have a VBA code that does not work for my data.
I am highly unfamiliar with VBA and do not understand the coding process. Any help would be appreciated. Thank you.

*Please see attached document.MASTER GRID.xlsNote: Names and information have been changed to reflect innaccurate data*

See if you can work with this. Everytime you click on the Master Sheet Tab it will be updated from all the other worksheets.



Attachment 171235

Wow, Thats perfect.
Thank you so much, words cannot express how impressed I am by your speed and help.

@ ospenc

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

ospenc

I added the sum of column W to cell C3. I dont think there should be any difference in code for 2007 and 2010. What line of code is highlighted when it errors?
Try this updated version. I noticed a few bugs in the first version.

Attachment 171338

A run time error does not appear in the 2010 version, but when using the 2007 version there is a run time error for

lastRow = Range("A" & Rows.CountLarge).End(xlUp).Row

@ospenc,
I saw your spreadsheet, now is the modified by multiple people or just yourself? If you are updating all the information, I know writing a code within the file will do the trick, however, if you are sharing then that code can become brittle in a hurry.

@ospenc

Both 2007 and 2010 use Rows.CountLarge, however 2003 does not. 2003 uses Rows.Count. I have 2007 and I get no errors..

---------- Post added at 09:59 AM ---------- Previous post was at 09:56 AM ----------

@mperrine,

I wrote the macro for the OP, Post number 6 should be the latest version of the code.

The document is modified by several individuals on a share drive. The most anyone will add is a new row to a certain state.

@mike

The document is working very well. You have found a solution to my dilemma. Thank you!