Combining sheets from multiple workbooks into master

I promise I searched, and tried to modify some of the vbscript I found here on the site that did similar things, but have not been able to make this work.

I have several workbooks that supervisors in a call center use to grade calls for quality.

Each supervisor has a seperate workbook, and after scoring the call, the supervisor runs a vb script that copies all of the data from the worksheet "observation" onto "sheet1" for storing the data.

So now I want to use another workbook to:

1. Copy each sheet1 from all of the supervisor workbooks onto a sheet named "cumulative" in a different workbook.

In a perfect world it would also:
2. Keep a count of how many observations were done by each supervisor each day
3. Keep a count of how many have been done week to date
4. Keep a count how many have been done total since January 1, 2009

Each workbook is kept on a network drive at s:\supervisors\qadata and named - for example QAformMelissa.xls

I'm okay with keeping the master sheet in the same directory if that makes it easier to accomplish this.

I've attached a scorecard workbook and the "master sheet" if that is helpful.
the scorecard is "fake" because the actual was larger than allowed by the forum, but hopefully you will be able to see what I'm doing.

Thanks much for taking the time to read and consider.

Question: After the QAForm???.xls is opened and imported, is the data on Sheet1 to be cleaned out so the next time you run the program it will not import the same data again?

Or does the manager clear it out?

Or should we add a field to the QAForm???.xls where we can mark each row as having been imported? Perhaps in column BD? This way each time it is run we only import rows with nothing in column BD.

Item 1:
Assuming the data in the manager's sheets does not get cleared out, then this macro will add and maintain the status of each row (imported or not) in column BD. It will import each sheet sequentially to the Cumulative database.

Option Explicit

Sub AccumulateData()
'JBeaucaire  (9/17/2009)
'Collects new data from various workbooks and adds to database
Dim NR As Long, LR As Long, fName As String
Dim wb As Workbook, wbData As Workbook
Application.ScreenUpdating = False

ChDir "S:\Supervisors\QAData"

Set wb = ThisWorkbook
wb.Activate
Sheets("Cumulative").Activate
NR = Range("A" & Rows.Count).End(xlUp).Row + 1

fName = Dir("QAForm*.xls")

    Do While Len(fName) > 0
        Set wbData = Workbooks.Open(fName)
        Sheets("Sheet1").Activate
        Range("BD1") = "Status"
        Range("A1:BD1").AutoFilter
        Range("A1:BD1").AutoFilter Field:=56, Criteria1:="="
        LR = Range("A" & Rows.Count).End(xlUp).Row
        If LR > 1 Then
            Range("A2:BC" & LR).Copy wb.Sheets("Cumulative").Range("A" & NR)
            Range("BD2:BD" & LR) = "Imported"
            ActiveSheet.AutoFilterMode = False
            wbData.Close True
        Else
            wbData.Close False
        End If
        NR = Range("A" & Rows.Count).End(xlUp).Row + 1
        fName = Dir
    Loop

Application.ScreenUpdating = True
Cells.Columns.AutoFit
End Sub

ITEMS 2-4:

List the supervisors in column A. Then insert and copy down the following formulas:

In B2:
=SUMPRODUCT(--(Cumulative!$B$2:$B$20000=$A2),--(INT(Cumulative!$C$2:$C$20000)=TODAY()-1))

In C2: (assuming Monday is the first day of the week)
=SUMPRODUCT(--(Cumulative!$B$2:$B$20000=$A2),--(Cumulative!$C$2:$C$20000>=TODAY()-MOD(TODAY()-2,7)))

In D2:
=SUMPRODUCT(--(Cumulative!$B$2:$B$20000=$A2),--(YEAR(Cumulative!$C$2:$C$20000)=YEAR(TODAY())))

This has been covered many times, you should try using the Forum Search engine e.g

http://www.excelforum.com/excel-prog...-into-one.html

Originally Posted by jaikin

Thank you that worked beautifully.

Excellent. Now analyze what we did in this script and compare it to what you were trying to do in amending other scripts. Of course there are 100s of ways to do the same thing, but by examining them in detail you can possibly learn what was wrong with your attempts.

Also, next time, post up the code you're working on so we can see where you're at so far, that might help us point out corrections to your own method more directly and speed up the learning curve for you, too.

======
If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].


(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)

I spent 2 days on the attached solution only to be back to this thread to find it SOLVED. I m still attaching it as an alternative solution. I hope the forum rules allow that. The solution assumes that all the files including the master are in one folder. Opening the master workbook fills the list automatically and summarizes the counts.

There's no rule against it, though posts after "solved" should, in my opinion, be added because the you sincerely believe the additional solution represents a better (read that as simpler or more accurate for some reason).

After reading through yours, it is definitely not simpler and the accuracy could only be equaleed.