Automated Summary Sheet

**TownerZC** · 11-01-2011, 09:42 AM

I work for a construction company and we are trying to transfer our job descoping process from a paper process to an electronic process. The basis of the process is that you have a series of checklists that you go through for each job; on each checklist is a series of items that could be in the contract. For each item there are three possible outcomes: we are responsible, a subcontractor is responsible, or it’s not in the contract. The idea is to create a workbook in excel that would have the entire series of checklists in it, then the user would fill the checklists out and the workbook would create one summary sheet that would be auto populated. I am an intermediate user of excel at best, but I believe this is something that could be done with a macro of some sort. I have attached a spreadsheet of two of the checklists, and what I am hoping the summary sheet will look like.

**arlu1201** · 11-01-2011, 11:18 AM

Will have something ready for you shortly.

**arlu1201** · 11-01-2011, 12:59 PM

Try this code. Do let me know if you face any issues.

Please Login or Register  to view this content.

**TownerZC** · 11-01-2011, 03:14 PM

Thank you so much, you just solved something that i have been trying to fix for about 3 weeks

. The only other question I have is, is their a way to reset the summary to blank before I run each cycle, I think this would alleviate the problem of duplicated entries on the summary sheet. For example if I run the macro in the example originally posted, I get two summary sheet entries, if i immediately run it again I should still get only the same two entries, but as is now it duplicates the first two entries making a total of four entries on the summary sheet.

**arlu1201** · 11-02-2011, 07:35 AM

This can be done. Add these few lines just after the DIM statements at the beginning of your code.

Please Login or Register  to view this content.

**TownerZC** · 11-02-2011, 08:18 AM

That works to clear the contents, but their seems to be some sort of flaw in where it starts to clear on the page, it should start clearing at row 3, currently it starts at row 1 clearing the titles of each column at the top of the page. Also the section column all the way on the left doesn't seem to be filling correctly when I generate the summary, it appears to be only filling two boxes and then leaving the rest blank. I wish I could remember more from the VB course that I took over 10 years ago