Code for summing data from a varying number of worksheets

[niftysquirrel]

Hello again everybody, thanks for all the help to date! I'm continuing work on a spreadsheet and I'm almost to the point of having it do everything I want it to do but I've hit a snag. I'm an experienced Excel user but I'm new to VBA and I'm still trying to get it all to make sense . I have a base worksheet called "Daily_Worksheet0" and I have a macro set up so that when the user hits a button, a new blanked out worksheet is created named sequentially (Daily_Worksheet1, Daily_Worksheet2, ...) and is added to the end of all existing worksheets. Now I'm working on a sheet called "SUMMARY" that should summarize the data from all sheets titled "Daily_Worksheet". I can't get this to work using formulas so I think I need to use VBA to do it, but I have absolutely no idea where to start and my searches online so far haven't gotten me anywhere.

This is what I've gotten to with a straight up formula: =SUMIF(Daily_Worksheet0!$A$12:$B$22,SUMMARY!$A9,Daily_Worksheet0!$I$12:$I$22). This does exactly what I want it to do but only works on one sheet, not all the "Daily_Worksheet" sheets that might be in the workbook. I tried a few things to get it to work but no luck yet. One attempt was creating a blank sheet called "Start" at the beginning of the "Daily_Worksheet" sheets and one called "End" that's always at the very end of all sheets and changing the formula to =SUMIF(Start:End!$A$12:$B$22,SUMMARY!$A9,Start:End!$I$12:$I$22) but that just comes up with an error.

Ultimately I'm trying to have a summary sheet that does this:

1) looks in each "Daily_Worksheet" at cells $A$12:$B$22 (one column but the cells each row are merged) where the user selects a labor category like Supervisor, Foreman, etc.
2) compares that to a cell in the "SUMMARY" sheet, cell A9 in this case, that contains a specific labor category
3)and then adds the number of hours worked by that specific labor category in cells $I$12:$I$22 from each "Daily_Worksheet".

The "Daily_Worksheet" sheets are used to list who worked on a site and how long they worked on a specific day, and I'm trying to get a summary for how many Supervisor hours were worked, how many Foreman hours were worked, etc. in total over all of the days.

I hope that all makes sense and it's not too much detail... If someone can tell me how to make my SUMIF formula work across multiple sheets or what code could be used to get the summing to happen I'd be eternally grateful!!

Thanks!!

[niftysquirrel]

Well after several hours of messing around with this I finally figured out something that works!!!! I cobbled together a formula from a bunch of places and managed to get it to do what I want without using VBA!!

[arlu1201]

If you solve a problem yourself before anyone else has responded, please take a moment to describe your solution, chances are some other member will benefit.

[niftysquirrel]

This is the code that I cobbled to get the data to copy over correctly (woohoo!). I can't really explain what it's doing, I just know that it works.

Please Login or Register  to view this content.